INDIRECT with VLOOKUP problem

[tehgargoth]

Say i open a new workbook, and in Sheet1
in A1 i put "Sheet2"
in B1 i put "5"
in C1 i put =INDIRECT("'"&$A$1&"'!A1")
in D1 i put =VLOOKUP(A2,B1:C1,2,FALSE)
in A2 i put "Apple"

on Sheet2 in A1 i simply put "Apple"

i get #N/A for my VLOOKUP
so is there any way to VLOOKUP looking for values obtained through INDIRECT?

[rylo]

Hi

Following from your data description but in Apple, Orange, Pear into sheet2!A1:A3 and 5,3,1 in sheet2!B1:B3.

Then:
sheet1!D1: =VLOOKUP(A2,INDIRECT("'"&A1&"'!A1:B3"),2,FALSE)

Your lookup range in your original function did not contain apple in the first column.


rylo