Forum Discussion

Flow2's avatar
Flow2
Copper Contributor
Oct 23, 2021

Use a cell value for the row number in Vlookup.

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 ce...
excel
HansVogelaar's avatar
HansVogelaar
MVP
Oct 23, 2021

Flow2 

Does this do what you want?

=IFERROR(-VLOOKUP(INDIRECT("'Sheet 3'!E"&D5),Sheet2!A:K,6,FALSE),0)
  • Flow2's avatar
    Flow2
    Copper Contributor
    Oct 23, 2021
    Hi Hans,

    Thank you for posting a potential answer, unfortunately that does not work, although I can see your logic.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 23, 2021

      Flow2 

      Could you attach a small sample workbook?

      • Flow2's avatar
        Flow2
        Copper Contributor
        Oct 23, 2021
        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.

Resources