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:

USA	DENVER	5/18/2018
USA	CRANSTON	Week of 5/7
USA	ATLANTA	TBD
USA	TAMPA	TBD
CAN	Richmond, BC	#VALUE!
CAN	Toronto ICD	#VALUE!
CAN	Montreal ICD	#VALUE!
EMEA	Cadishead, UK	#VALUE!
EMEA	Widnes, UK	#VALUE!
EMEA	Belgium	#VALUE!
EMEA	Genay, France	#VALUE!
EMEA	Morolo, IT	#VALUE!
LATAM	Guadalajara	#VALUE!
LATAM	Recife	#VALUE!

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[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