How can I use nested VLOOKUPs?

[Bgs1]

I’m trying to give the lookup array for a VLOOKUP as a named array coming from another VLOOKUP.

Namely:

1. This works: VLOOKUP(C2, VendorExams, 3, 0)

I get as output CAT_exams which is the name of an array.

2. This works too: VLOOKUP(C2&D2, CAT_names, 4, 0)

I get as output the desired cell value from the CAT_names array.

3. Nesting them with (or without for that matter) INDIRECT, doesn’t work: INDIRECT(VLOOKUP(C2&D2, VLOOKUP(C2, VendorExams, 3, 0), 4, 0))

I get an 504 error which indicates to me that the nested VLOOKUPs output is not understood.

Is this not-working-as-intended, a bug or am I doing something in a wrong way?

Using 4.2.3 if it has any relevance.

[Lupp]

Don’t have your sheet. That should be much more helpfull than a description from a possibly biased point of view.

(Editing: Sorry! Just noticed you might not have been allowed to upload a file.)

As you got CAT_exams as a result from looking up an array I suppopse this was a string. You have to use INDIRECT() for the proper purpose: turning a string into a reference.

=VLOOKUP(C2&D2, INDIRECT(VLOOKUP(C2, VendorExams, 3, 0)), 4, 0)) should work.