Vlookup using a dropdwn list, check different tables based on drop
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 |
Vlookup using a dropdwn list, check different tables based on drop
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 |
Vlookup using a dropdwn list, check different tables based on drop
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 |
All times are GMT +1. The time now is 07:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com