Vlookup using a dropdwn list, check different tables based on drop

M

Manny

Vlookup using a dropdown list, check different tables based on dropdown list
selection.

Country Dropdown list, 10 different countries (for starts). Each country has
a different course ID table. Each course ID table has approx 200+ courses.
Each Course ID table has 2 columns; 1st is course ID, second is credit value
for course ID.

I set-up a country list with a numerical value (1st country =1, 2nd country
=2, 3rd country =3, etc). I figured setting a numerical value would set me
up for using IF function, coupled with vlookup.

Course ID will be entered in A5:A45 (manually). I want value of course ID
to appear in column C (C5:C45), based on country selected from dropdown, and
course ID entered in A5:A45.
 
M

Manny

I figured this out about an hour after my post, thought I'd share.

The dropdown has multiple countries. Cell X5 is where droplist places
selection from dropdown list. Cell Y5 has vlookup which looks up country
name from cell X5 in country table and returns a value, this is the value
referred to in main formula (see $Y$5 below).

BTW, data entry begins in B11:B51 (not A5:A45 as mentioned earlier), formula
itself is placed in D11:D51.

=IF(AND(B11>0,$Y$5=1),VLOOKUP(B11,AWCourseTable,2,FALSE),IF(AND(B11>0,$Y$5=2),VLOOKUP(B11,ATCourseTable,2,FALSE),IF(AND(B11>0,$Y$5=3),VLOOKUP(B11,BRCourseTable,2,FALSE),"")))

Hope someone out there finds this useful.
 
T

T. Valko

You can reduce that to:

=IF(AND(B11>0,OR($Y$5={1,2,3})),VLOOKUP(B11,CHOOSE($Y$5,AWCourseTable,ATCourseTable,BRCourseTable),2,0),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top