HLP! Nesting Problem??

[dee]

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),"ERROR")))))))

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")))))))

 

[Roger Govier]

Hi

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

 

[dee]

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

Hi

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

 

[JMB]

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/
=IF(ISNUMBER(MATCH($AA16,$V$19:$V$25,0)),VLOOKUP($AA16,INDIRECT($Y16),VLOOKUP($AA16,$V$19:$W$25,2,0),0),"ERROR")

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?

 

[dee]

Thanks to both of you, I have a couple of options.

Thanks!