Using Indirect in VLookup

[Justinetme]

I want to use "Indirect" in my VLookup to give the starting range to search.

I want to substitute, Indirect(E16) for $B$1 in the following formula.

Any help would be greatly appreciated.
Thank you.


=IF(ISERROR(VLOOKUP($C$8,'Relativity Summary'!$B$1:$J$65536,9,FALSE)),"No More",VLOOKUP($C$8,'Relativity Summary'!$B$1:$J$65536,9,FALSE))


Justine

[Pete_UK]

Try it this way:

=IF(ISERROR(VLOOKUP($C$8,INDIRECT("'Relativity Summary'!"&E16&":$J$65536"),9,FALSE)),"No More",VLOOKUP($C$8,INDIRECT("'Relativity Summary'!"&E16&":$J$65536"),9,FALSE))

where it is assumed that E16 contains something that looks like a cell address, like B1, B15 etc. (no need for the $ symbols, as it is text).

Hope this helps.

Pete

[etaf]

so to use in your formula

=IF(ISERROR(VLOOKUP($C$8,indirect("'Relativity Summary'!"&E16&":$J$65536"),9,FALSE)),"No More",VLOOKUP($C$8,indirect("'Relativity Summary'!"&E16&":$J$65536"),9,FALSE))

[Justinetme]

Thank you sooo much. It's perfect!!!

Justine

[etaf]

your welcome

[Tony Valko]

What version of Excel are you using?

What type of data does the formula normally return? Is it TEXT? Numeric? Could be both? Something else?