Help with nested if statements with vlookups

[tiltz]

I am trying to have a multiple criteria if statement to vlookup a value based region (which each is on a different tab). Each of the if statements work fine on their own, but not combined. What am i doing wrong here:

=OR(IF(A2="USA",VLOOKUP(B:B,USA!A:B,2,FALSE),"NA"),IF(A2="CAN",VLOOKUP(Canada!A:B,2,FALSE),"NA"),IF(Tracker!A2="EMEA",VLOOKUP(EMEA!A:B,2,FALSE),"NA"),IF(Tracker!A2="LATAM",VLOOKUP(LATAM!A:B,2,FALSE),"NA"),IF(Tracker!A2="Non-integrated",VLOOKUP(Non-Integrated!A:B,2,FALSE),"NA"))

 

[jtakw]

Hi,

You started your formula with the OR function, which will give you either TRUE or FALSE as a result.

Try Nesting your IFs, replace the "Value if False" part of the 1st IF statement (where it says "NA") with the Next IF statement, and so on:

 

[tiltz]

Just tried this still giving me an error:

=IF(A2="USA",VLOOKUP(B:B,USA!A:B,2,FALSE),IF(A2="CAN",VLOOKUP(Canada!A:B,2,FALSE),IF(Tracker!A2="EMEA",VLOOKUP(EMEA!A:B,2,FALSE),IF(Tracker!A2="LATAM",VLOOKUP(LATAM!A:B,2,FALSE),IF(Tracker!A2="Non-integrated",VLOOKUP(Non-Integrated!A:B,2,FALSE),"NA"))

 

[wideboydixon]

Your VLOOKUP functions don't contain a value to lookup? It's not 100% clear what you're trying to do; the OR() at the start can't be right. So it's something like this:

If Tracker!A2 = "USA" then lookup {some value} in column A of sheet USA and return the value from column B. If the value doesn't exist then return "NA"
Similar conditions for CAN, EMEA, LATAM and Non-integrated

The question is, what is {some value} - is it in cell B2? Also, what happens if A2 is not one of these values? Pure guesswork here but what about:

=IFERROR(VLOOKUP(Tracker!$B2,INDIRECT(Tracker!$A2&"!$A:$B"),2,FALSE),"NA")

WBD

 

[jtakw]

Just tried this still giving me an error:

=IF(A2="USA",VLOOKUP(B:B,USA!A:B,2,FALSE),IF(A2="CAN",VLOOKUP(Canada!A:B,2,FALSE),IF(Tracker!A2="EMEA",VLOOKUP(EMEA!A:B,2,FALSE),IF(Tracker!A2="LATAM",VLOOKUP(LATAM!A:B,2,FALSE),IF(Tracker!A2="Non-integrated",VLOOKUP(Non-Integrated!A:B,2,FALSE),"NA"))

You're missing a few closing brackets ) for the IFs, it should look something like below, I can't test the formula as I don't want to create all those Sheets and dummy data, see if it works:

Excel 2010
	C
1	#REF!
Sheet30

Cell Formulas
Range	Formula
C1	=IF(A2="USA",VLOOKUP(B:B,USA!A:B,2,FALSE),IF(A2="CAN",VLOOKUP(Canada!A:B,2,FALSE),IF(Tracker!A2="EMEA",VLOOKUP(EMEA!A:B,2,FALSE),IF(Tracker!A2="LATAM",VLOOKUP(LATAM!A:B,2,FALSE),IF(Tracker!A2="Non-integrated",VLOOKUP(Non-Integrated!A:B,2,FALSE),"NA")))))

 

[tiltz]

The formula is no longer erroring, but it's only pulling in the info from the USA tab:

[TABLE="width: 398"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]USA[/TD]
[TD]DENVER[/TD]
[TD="align: right"]5/18/2018[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]CRANSTON[/TD]
[TD]Week of 5/7[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]ATLANTA[/TD]
[TD]TBD[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]TAMPA[/TD]
[TD]TBD[/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]Richmond, BC[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]Toronto ICD[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]Montreal ICD[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]Cadishead, UK[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]Widnes, UK[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]Belgium[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]Genay, France[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]EMEA[/TD]
[TD]Morolo, IT[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]LATAM[/TD]
[TD]Guadalajara [/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]LATAM[/TD]
[TD]Recife[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Did you try my formula?

=IFERROR(VLOOKUP(Tracker!$B2,INDIRECT(Tracker!$A2&"!$A:$B"),2,FALSE),"NA")

WBD

 

[tiltz]

Did you try my formula?

=IFERROR(VLOOKUP(Tracker!$B2,INDIRECT(Tracker!$A2&"!$A:$B"),2,FALSE),"NA")

WBD

That did work - except for the tab call "non-integrated"

 

[jtakw]

My post #5 was ONLY to correct your brackets, it's Not meant to be a working formula as I stated.

I didn't even really look thru the formula, but as WBD pointed out, you're missing the LOOKUP values in your formula, so correct them as suggested by WBD.

 

[wideboydixon]

Ah. Shame they don't match. Try this then:

=IFERROR(VLOOKUP($B2,CHOOSE(MATCH($A2,{"USA","CAN","EMEA","LATAM","Non-integrated"},0),USA!$A:$B,CAN!$A:$B,EMEA!$A:$B,LATAM!$A:$B,'Non-Integrated'!$A:$B),2,FALSE),"NA")

WBD