NESTED VLOOKUP 1

[pmcmicha]

I have an issue with a nested VLOOKUP formula.

=IF(ISBLANK(E50),"+0",VLOOKUP(COUNTIF(($E$50:E50),E50),BAB,VLOOKUP((E50),VLOOKUP((C50),CLASS_SELECT,2),2))+O48)

CLASS_SELECT is a named range which is set as follows:
VALUE ==> NAMED_RANGE

The problem is with the section of the formula in red, this part returns the correct NAMED_RANGE, but it does so with quotes. This then causes the parent VLOOKUP (in green and underlined) to fail.

I have attempted to use different techniques such as CONCATENATE, INDIRECT and what not, but then VLOOKUP complains about the formula being volatile.

Any help would be greatly appreciated. Thanks in advance.

 

[GlennUK]

I use INDIRECT in this way a lot and don't usually have problems. Are you sure that the error is happening the way you think? Where are the quotes coming from? If you have the names of named-ranges stored in the CLASS_SELECT list, surely they wouldn't have quotes!!!!



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Just to confirm - I have also used INDIRECT many times like this and never had any issues.

What version of excel ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[pmcmicha]

Excel Version: XP

The quotes are coming from the named range.

CLASS_SELECT=(DATA!$A$2:$B$7)

Here is how it shows up when I troubleshoot the formula:
CLASS_SELECT={"TEST1","R_TEST1";"TEST2","R_TEST2"} (etc.)


It seems to be adding the quotes automatically. When I insert INDIRECT(CLASS_SELECT), the formula then complains about it being volatile. Am I using this incorrectly?

 

[xlbo]

Don't think INDIRECT can refer to MULTIPLE named ranges at the same time - you must choose 1

so if I had "R_TEST1"
in cell A2 then I would expect

=VLOOKUP(A1,INDIRECT(A2),2,false)

to work, as long as R_TEST1 had at least 2 columns

Indeed, I have just tested and it works fine (XP also btw)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

You are using the INDIRECT in the wrong place if ( and I'm doing a little guesswork here ) CLASS_SELECT contains a list of names that are the names of named ranges.

Try
=IF(ISBLANK(E50),"+0",VLOOKUP(COUNTIF(($E$50:E50),E50),BAB,VLOOKUP((E50),INDIRECT(VLOOKUP((C50),CLASS_SELECT,2)),2))+O48)

which does the INDIRECT on the returned value from CLASS_SELECT and uses that as a name, as it were.

Hope my guessing is right, if not, ignore what I've said.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[pmcmicha]

Thanks to both of you for you assistance. The solution that GlennUK provided works like a champ.