Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1142576%22%20slang%3D%22en-US%22%3EVLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142576%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%20I%20really%20need%20help%20with%20the%20SPILL%20error.%20I%20know%20it's%20been%20an%20issue%20since%20the%20update%20last%20year%20and%20the%20solution%20is%20to%20select%20a%20specified%20range%20rather%20than%20an%20entire%20column%2C%20but%20the%20result%20only%20partially%20works.%20See%20spreadsheet%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20lookup%20a%20text%20value%20based%20on%20two%20criteria%20in%20two%20different%20columns.%20eg%3A%3C%2FP%3E%3CP%3E%3DIF(G6%3DA6%2CVLOOKUP(B6%2CH%3AJ%2C3%2C0)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168270iC5AEC93FDB103330%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Screenshot%202020-01-31%20at%2010.59.59.png%22%20title%3D%22Screenshot%202020-01-31%20at%2010.59.59.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20works%20but%20only%20if%20the%20matching%20values%20are%20on%20the%20same%20line%2C%20which%20is%20rarely%20the%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20the%20update%20I%20would%20have%20used%20this%20which%20now%20returns%20the%20SPILL%20error%3A%3C%2FP%3E%3CP%3E%3DIF(G%3AG%3DA2%2CVLOOKUP(B2%2CH%3AJ%2C3%2C0)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20also%20trying%20to%20concatenate%20the%20names%20together%20but%20can't%20find%20a%20formula%20that%20will%20work%20with%20a%20VLOOKUP.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168271i0FE54FCF6A1DC1DD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Screenshot%202020-01-31%20at%2011.02.01.png%22%20title%3D%22Screenshot%202020-01-31%20at%2011.02.01.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1142576%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142603%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142603%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F526806%22%20target%3D%22_blank%22%3E%40A819A1L%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%20instead%3A%3C%2FP%3E%3CPRE%3E%3DIF(OR(A2%3DG%3AG)%2CVLOOKUP(B2%2CH%3AJ%2C3%2C0)%2C0)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20this%20one%20below%20which%20is%20faster%3A%3C%2FP%3E%3CPRE%3E%3DIF(MATCH(A2%2CG%3AG%2C0)%2CVLOOKUP(B2%2CH%3AJ%2C3%2C0))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142629%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BYes%2C%20the%20second%20one%20works!%20Thank%20You!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20on%20how%20to%20concatenate%20the%20names%20together%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142658%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142658%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F526806%22%20target%3D%22_blank%22%3E%40A819A1L%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20Excel%20version%20allows%20it%2C%20this%20one%20will%20work%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2C%2CUNIQUE(IFERROR(INDEX(J%3AJ%2CIFERROR(1%2F(B55%3D%24H%242%3A%24H%24103)*ROW(%24H%242%3A%24H%24103)%2C%22%22)%2C1)%2C%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142708%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F526806%22%20target%3D%22_blank%22%3E%40A819A1L%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2C1%2CUNIQUE(FILTER(J%3AJ%2C(G%3AG%3D%24A2)*(H%3AH%3D%24B2)%2C%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142730%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142730%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3EIt%20works%20on%20bringing%20the%20text%20together%20however%2C%20the%20results%20are%20incorrect%20as%20I'm%20trying%20to%20separate%20the%20resource%20criteria%20i.e.%20return%20the%20operator%20to%20the%20associated%20machine.%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3EIt%20works%20on%20bringing%20the%20text%20together%20however%2C%20the%20results%20are%20incorrect%20as%20I'm%20trying%20to%20separate%20the%20resource%20criteria%20i.e.%20return%20the%20operator%20to%20the%20associated%20machine.%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142736%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20now%20that%20my%20formula%20only%20looks%20at%20JobNum.%20So%20it%20will%20go%20wrong%20if%20you%20have%20the%20same%20JobNum%20occurs%20with%20multiple%20Resources.%20Since%20this%20is%20the%20case%2C%20my%20solution%20is%20useless.%20But%20it%20was%20fun%20to%20create%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142740%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20%26nbsp%3BThis%20formula%20works%20best.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142796%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20multiple%20criteria%20(trying%20to%20work%20around%20SPILL%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F526806%22%20target%3D%22_blank%22%3E%40A819A1L%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome%2C%20thank%20you%20the%20confirmation.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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)

 

Screenshot 2020-01-31 at 10.59.59.png

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.

 

Screenshot 2020-01-31 at 11.02.01.png

Thanks in advance for any help!

 

Abi

 

8 Replies
Highlighted

@A819A1L

 

Hi,

 

Try this formula instead:

=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

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

 

Any ideas on how to concatenate the names together?

 

Abi

Highlighted

@A819A1L 

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

@A819A1L 

Another variant

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

 

Highlighted
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

@Sergei Baklan 

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

@Sergei Baklan 

 

Thanks.  This formula works best.

Highlighted

@A819A1L 

You are welcome, thank you the confirmation.