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.
--
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(B110,$Y$5=1),VLOOKUP(B11,AWCourseTable,2, FALSE),IF(AND(B110,$Y$5=2),VLOOKUP(B11,ATCourseTa ble,2,FALSE),IF(AND(B110,$Y$5=3),VLOOKUP(B11,BRCo urseTable,2,FALSE),"")))

Hope someone out there finds this useful.

--
Manny


"Manny" wrote:

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.
--
Manny

You can reduce that to:

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

--
Biff
Microsoft Excel MVP


"Manny" wrote in message
...
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(B110,$Y$5=1),VLOOKUP(B11,AWCourseTable,2, FALSE),IF(AND(B110,$Y$5=2),VLOOKUP(B11,ATCourseTa ble,2,FALSE),IF(AND(B110,$Y$5=3),VLOOKUP(B11,BRCo urseTable,2,FALSE),"")))

Hope someone out there finds this useful.

--
Manny


"Manny" wrote:

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.
--
Manny