• 667K Members
• 4,901 Online
• 821K Conversations

VLOOKUP with multiple criteria (trying to work around SPILL error)

Highlighted
Occasional Contributor

VLOOKUP with multiple criteria (trying to work around SPILL error)

Hi. I really need help with the SPILL error. I know it's been an issue since the update last year and the solution is to select a specified range rather than an entire column, but the result only partially works. See spreadsheet attached.

I'm trying to lookup a text value based on two criteria in two different columns. eg:

=IF(G6=A6,VLOOKUP(B6,H:J,3,0),0)

This works but only if the matching values are on the same line, which is rarely the case.

Before the update I would have used this which now returns the SPILL error:

=IF(G:G=A2,VLOOKUP(B2,H:J,3,0),0)

I'm also trying to concatenate the names together but can't find a formula that will work with a VLOOKUP.

Thanks in advance for any help!

Abi

8 Replies
Highlighted

Re: VLOOKUP with multiple criteria (trying to work around SPILL error)

@A819A1L

Hi,

`=IF(OR(A2=G:G),VLOOKUP(B2,H:J,3,0),0)`

Or this one below which is faster:

`=IF(MATCH(A2,G:G,0),VLOOKUP(B2,H:J,3,0))`

Hope that helps

Highlighted

Re: VLOOKUP with multiple criteria (trying to work around SPILL error)

@Haytham Amairah Yes, the second one works! Thank You!

Any ideas on how to concatenate the names together?

Abi

Highlighted

Re: VLOOKUP with multiple criteria (trying to work around SPILL error)

If your Excel version allows it, this one will work:

``=TEXTJOIN(", ",,UNIQUE(IFERROR(INDEX(J:J,IFERROR(1/(B55=\$H\$2:\$H\$103)*ROW(\$H\$2:\$H\$103),""),1),"")))``

Highlighted

Re: VLOOKUP with multiple criteria (trying to work around SPILL error)

Another variant

``=TEXTJOIN(", ",1,UNIQUE(FILTER(J:J,(G:G=\$A2)*(H:H=\$B2),"")))``

Highlighted

Re: VLOOKUP with multiple criteria (trying to work around SPILL error)

Spoiler
It works on bringing the text together however, the results are incorrect as I'm trying to separate the resource criteria i.e. return the operator to the associated machine.
Highlighted

Re: VLOOKUP with multiple criteria (trying to work around SPILL error)

See now that my formula only looks at JobNum. So it will go wrong if you have the same JobNum occurs with multiple Resources. Since this is the case, my solution is useless. But it was fun to create it.

Highlighted

Re: VLOOKUP with multiple criteria (trying to work around SPILL error)

Thanks.  This formula works best.

Highlighted

Re: VLOOKUP with multiple criteria (trying to work around SPILL error)

You are welcome, thank you the confirmation.