I believe your problem is you exceeded excels limit of 7 nested function
levels.

I would try using Roger's named range approach. You could set up a table to
identify the column number to return. Lets say in V19:W25

Canada 5
France 5
Germany 5
Italy 5
Swiss 5
UK 5
USA 3


and the formula s/b
=IF(ISNUMBER(MATCH($AA16,$V$19:$V$25,0)),VLOOKUP($ AA16,INDIRECT($Y16),VLOOKUP($AA16,$V$19:$W$25,2,0) ,0),"ERROR")




"dee" wrote:

I had thought of that, but they are in separate worksheets and the column
number referenced may be different, so I don't think it will work?
--
Thanks!

Dee


"Roger Govier" wrote:

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