Use a cell value for the row number in Vlookup.

Question

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.

hansvogelaar · Answer

Flow2&nbsp;
Does this do what you want?
=IFERROR(-VLOOKUP(INDIRECT("'Sheet 3'!E"&amp;D5),Sheet2!A:K,6,FALSE),0)

mtarler · Answer

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.

flow2 · Answer

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

hansvogelaar · Answer

Flow2&nbsp;
Could you attach a small sample workbook?

flow2 · Answer

Hi Hans,Thank you for your help with this, it has given me the clue for the answer;=IFERROR(-VLOOKUP(INDIRECT("'SHEET 3'!I"&amp;D5),'SHEET 2'!A:K,6,FALSE),0)The only difference to your is the ' around SHEET 2This will save me hours of typing, as well as the risk of typos.I hope the formula here will help others too.

Forum Discussion

Use a cell value for the row number in Vlookup.

9 Replies

Resources