Vlookup/IF question

B

biglb79

Active Member
Joined
Oct 17, 2007
Messages
299
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 #
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Consider:

ABCDE
1CountryID #File #
2US1aa
3CA2ww
4XX3#REF!c

<tbody>
</tbody>
Sheet5​

Worksheet Formulas
Formula
C2=VLOOKUP(B2,INDIRECT(A2&"!A2:B10"),2,FALSE)
D2=VLOOKUP(B2,INDIRECT(LOOKUP(A2,{"CA","US"},{"'CA Sheet'","'US Sheet'"})&"!A2:B10"),2,FALSE)

<tbody>
</tbody>

<tbody>
</tbody>



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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

DPChristman
  • Solved
Creating an autofill with multiple sources
Replies
7
Views
259
Peter_SSs
Peter_SSs
O
  • Solved
Counting instances of unique entries, with conditions
Replies
1
Views
392
ollyhughes1982
O
S
  • Solved
VBA Error Logging to tab on same Spreadsheet
2
Replies
13
Views
585
SBest
S
S
  • Solved
Dynamic Change the Xlookup Return Array
Replies
3
Views
575
Fluff
Fluff
J
  • Question
How can I create a list from a single layer of a directory?
Replies
6
Views
315
PeteWright
PeteWright

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,093
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top