Vlookup/IF question

[biglb79]

how would I create a formula to lookup an ID number in column C on a US directory tab or CA directory tab if the country code in column B is either US or CA? Below are the three columns I'm using and I want the file number to look in the specific directory tab based on what country code is used in column B

Country ID # File #

 

[Eric W]

Consider:

	A	B	C	D	E
Country	ID #	File #
US	a	a
CA	w	w
XX	c

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]#REF![/TD]

[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=VLOOKUP(B2,INDIRECT(A2&"!A2:B10"),2,FALSE)[/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=VLOOKUP(B2,INDIRECT(LOOKUP(A2,{"CA","US"},{"'CA Sheet'","'US Sheet'"})&"!A2:B10"),2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If your tab names are the same as the state, then you can use the formula in C2. If you need to lookup the tab name based on the value in A2, then perhaps the D2 formula would work for you. Both formulas assume that your lookup table in in the same range on both sheets, A2:B10.

Note that if you use the second formula, the values in the first array {"CA","US"} must be in alphabetical order. Also, if you get a "not found" condition, like in row 4, it will use the last value in the array. So if you have a long list to work with, there might be better ways.

 

[biglb79]

thank you so much! you rock!

 

[Eric W]

Happy to help!