Convert an array (text string) to an array (usable form) for Lookup (H/Vlookup)

[sangviet]

Dear all,

Please help with below case:

Assume I have different ranges of data, then I marked them as:
(Col.1) (Col.2)
(Row.1) 1 $A$1:$D$7
(Row.2) 2 $B$14:$E$21

Then I use Vlookup function, with target to auto-change the array of condition:
=VLOOKUP(1,VLOOKUP(1,$E$9:$F$10,2,FALSE),2,FALSE) -> result = #VALUE!

The problem is:
VLOOKUP(1,$E$9:$F$10,2,FALSE) return the exact results, yet in text format
Then the "mother" VLOOKUP cannot detect the range and show error

I want to convert the text array into usable array, any solution?

Pls see attachments for more details

Picture1.jpgPicture2.jpg

[MrShorty]

It appears that you want to nest your inner VLOOKUP() inside of an INDIRECT() function: https://support.office.com/en-us/art...1-92b6306fa261

[dangelor]

Try using the INDIRECT function...

[sangviet]

Sorry, not yet clear enough

I don't want to put array one by one, but auto detect from existing referent table

I tried INDIRECT but the description is too short

Anyway, thanks for comment

[MrShorty]

Something like =VLOOKUP(1,INDIRECT(VLOOKUP(1,$E$9:$F$10,2,FALSE)),2,FALSE) is what I had in mind.