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

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

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