Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using VLOOKUP for one option, if this option is not valid than sec
Outline:
5 columns A-E: A B C D E Australia 0.0507 Australia 0.1242 Belgium 0.0254 #N/A Canada 0.2356 Canada 0.0256 First look in column D&E if Belgium is available than take corresponding number (E). Second if not, look in column A&B if Belgium is available and take corresponding number (B). This is how far I got (only it doen't work (yet!): =IF(((VLOOKUP(B3;$E$2:$F$355;2;FALSE))="#N/A");C3;(VLOOKUP(B3;$B$2:$C$1020;2))) Please help Edd |
#2
|
|||
|
|||
Hi Edd,
=IF(ISNA(VLOOKUP(B3;$E$2:$F$355;2;FALSE));VLOOKUP( B3;$B$2:$C$1020;2);VLOOKUP(B3;$E$2:$F$355;2;FALSE) ) Are you sure you don't need the FALSE in the second lookup? -- Kind Regards, Niek Otten Microsoft MVP - Excel "Edd" wrote in message ... Outline: 5 columns A-E: A B C D E Australia 0.0507 Australia 0.1242 Belgium 0.0254 #N/A Canada 0.2356 Canada 0.0256 First look in column D&E if Belgium is available than take corresponding number (E). Second if not, look in column A&B if Belgium is available and take corresponding number (B). This is how far I got (only it doen't work (yet!): =IF(((VLOOKUP(B3;$E$2:$F$355;2;FALSE))="#N/A");C3;(VLOOKUP(B3;$B$2:$C$1020;2))) Please help Edd |
#3
|
|||
|
|||
BTW, If you use B3 to search in B2:C1020, you'll always have the same hit at
B3! -- Kind Regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... Hi Edd, =IF(ISNA(VLOOKUP(B3;$E$2:$F$355;2;FALSE));VLOOKUP( B3;$B$2:$C$1020;2);VLOOKUP(B3;$E$2:$F$355;2;FALSE) ) Are you sure you don't need the FALSE in the second lookup? -- Kind Regards, Niek Otten Microsoft MVP - Excel "Edd" wrote in message ... Outline: 5 columns A-E: A B C D E Australia 0.0507 Australia 0.1242 Belgium 0.0254 #N/A Canada 0.2356 Canada 0.0256 First look in column D&E if Belgium is available than take corresponding number (E). Second if not, look in column A&B if Belgium is available and take corresponding number (B). This is how far I got (only it doen't work (yet!): =IF(((VLOOKUP(B3;$E$2:$F$355;2;FALSE))="#N/A");C3;(VLOOKUP(B3;$B$2:$C$1020;2))) Please help Edd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Second option button disappears when I make another column | Excel Worksheet Functions | |||
Filter option | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |