Discussion Re: Use a cell value for the row number in Vlookup. in Excel
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877733#M119195
<P><LI-USER uid="1193928"></LI-USER> </P>
<P>Your first post had Sheet2 instead of Sheet 2...</P>Sat, 23 Oct 2021 20:48:13 GMTHans Vogelaar2021-10-23T20:48:13ZUse a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877075#M119156
<P>On sheet 1 in row 5 and column h for example </P><P> the formula, =IFERROR(-VLOOKUP('Sheet 3'!E13,Sheet2!A:K,6,FALSE),0)</P><P> </P><P>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"</P><P> </P><P>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.</P><P>If anyone knows of a tidy way of making that happen it would save me a lot of typing.</P><P>Unfortunately the values in column D are not sequential.</P>Sat, 23 Oct 2021 14:00:46 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877075#M119156Flow22021-10-23T14:00:46ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877120#M119157
<P><LI-USER uid="1193928"></LI-USER> </P>
<P>Does this do what you want?</P>
<LI-CODE lang="excel-formula">=IFERROR(-VLOOKUP(INDIRECT("'Sheet 3'!E"&D5),Sheet2!A:K,6,FALSE),0)</LI-CODE>Sat, 23 Oct 2021 14:20:16 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877120#M119157Hans Vogelaar2021-10-23T14:20:16ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877474#M119174
Hi Hans,<BR /><BR />Thank you for posting a potential answer, unfortunately that does not work, although I can see your logic.Sat, 23 Oct 2021 17:22:23 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877474#M119174Flow22021-10-23T17:22:23ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877523#M119179
<P><LI-USER uid="1193928"></LI-USER> </P>
<P>Could you attach a small sample workbook?</P>Sat, 23 Oct 2021 18:07:30 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877523#M119179Hans Vogelaar2021-10-23T18:07:30ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877654#M119188
Hi Hans,<BR /><BR />Thank you for your help with this, it has given me the clue for the answer;<BR />=IFERROR(-VLOOKUP(INDIRECT("'SHEET 3'!I"&D5),'SHEET 2'!A:K,6,FALSE),0)<BR />The only difference to your is the ' around SHEET 2<BR /><BR />This will save me hours of typing, as well as the risk of typos.<BR />I hope the formula here will help others too.Sat, 23 Oct 2021 19:44:59 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877654#M119188Flow22021-10-23T19:44:59ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877733#M119195
<P><LI-USER uid="1193928"></LI-USER> </P>
<P>Your first post had Sheet2 instead of Sheet 2...</P>Sat, 23 Oct 2021 20:48:13 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877733#M119195Hans Vogelaar2021-10-23T20:48:13ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877761#M119198
Hi Hans,<BR /><BR />The key difference is the punctuation around the name of the tab.<BR />e.g 'Tab Name'Sat, 23 Oct 2021 21:16:29 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877761#M119198Flow22021-10-23T21:16:29ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877777#M119199
<P><LI-USER uid="1193928"></LI-USER> </P>
<P>I know that, but never mind...</P>Sat, 23 Oct 2021 21:33:42 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877777#M119199Hans Vogelaar2021-10-23T21:33:42ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877971#M119212
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.Sun, 24 Oct 2021 00:48:07 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2877971#M119212mtarler2021-10-24T00:48:07ZRe: Use a cell value for the row number in Vlookup.
https://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2878329#M119230
<P><LI-USER uid="570951"></LI-USER> </P>
<P>Indeed.</P>Sun, 24 Oct 2021 09:20:43 GMThttps://techcommunity.microsoft.com/t5/excel/use-a-cell-value-for-the-row-number-in-vlookup/m-p/2878329#M119230Hans Vogelaar2021-10-24T09:20:43Z