Hi

Why not create named ranges for Canada, France Germany etc. then just
use
=VLOOKUP($AA16,INDIRECT($Y16),5,FALSE),


--
Regards

Roger Govier


"dee" wrote in message
...
Hi,

Am wondering why the first formula below works fine, but when I modify
it
with an INDIRECT in the last line, it keep telling me I have an error.

THIS WORKS:
=IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE),
IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE),
IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE),
IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE),
IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE),
IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE),
IF($Y16="USA",VLOOKUP($AA16,USA!$B:$D,3,FALSE),"ER ROR")))))))

THIS DOESN'T WORK:
=IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE),
IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE),
IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE),
IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE),
IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE),
IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE),
IF($Y16="USA",VLOOKUP($AA16,INDIRECT($H$1&"!$B:$D" ),3,FALSE),"ERROR")))))))
--
Thanks!

Dee