Oct 23 2021 07:00 AM
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.
Oct 23 2021 07:20 AM
Does this do what you want?
=IFERROR(-VLOOKUP(INDIRECT("'Sheet 3'!E"&D5),Sheet2!A:K,6,FALSE),0)
Oct 23 2021 10:22 AM
Oct 23 2021 11:07 AM
Could you attach a small sample workbook?
Oct 23 2021 12:44 PM
Oct 23 2021 01:48 PM
Your first post had Sheet2 instead of Sheet 2...
Oct 23 2021 02:16 PM
Oct 23 2021 02:33 PM
I know that, but never mind...
Oct 23 2021 05:48 PM