Use a cell value for the row number in Vlookup.

Copper Contributor

On sheet 1 in row 5 and column h for example 

 the formula, =IFERROR(-VLOOKUP('Sheet 3'!E13,Sheet2!A:K,6,FALSE),0)

 

However I would like to replace the number in E13, with a value derived from cell D of the same row as the formula, so the E13 becomes E "value of cell D5"

 

Ideally I can then drag the formula into other rows so that the value of the row used in the look up changes according to the value in column D of the row where the formula sits.

If anyone knows of a tidy way of making that happen it would save me a lot of typing.

Unfortunately the values in column D are not sequential.

9 Replies

@Flow2 

Does this do what you want?

=IFERROR(-VLOOKUP(INDIRECT("'Sheet 3'!E"&D5),Sheet2!A:K,6,FALSE),0)
Hi Hans,

Thank you for posting a potential answer, unfortunately that does not work, although I can see your logic.

@Flow2 

Could you attach a small sample workbook?

Hi Hans,

Thank you for your help with this, it has given me the clue for the answer;
=IFERROR(-VLOOKUP(INDIRECT("'SHEET 3'!I"&D5),'SHEET 2'!A:K,6,FALSE),0)
The only difference to your is the ' around SHEET 2

This will save me hours of typing, as well as the risk of typos.
I hope the formula here will help others too.

@Flow2 

Your first post had Sheet2 instead of Sheet 2...

Hi Hans,

The key difference is the punctuation around the name of the tab.
e.g 'Tab Name'

@Flow2 

I know that, but never mind...

Hans, you couldn't have known because Flow2's original post didn't use ' around Sheet2 but did use it around 'Sheet 3' so it would appear his Sheet2 name didn't have a space ... but then later it did.