Search code examples
variablesreferencecellvlookup

Variable Cell Reference in VLOOKUP lookup_value

My question is very simple, but I cannot come to the answer for a long time.

In my first sheet I have such data:

Sheet1

My second sheet has VLOOKUP function, the table looks like this:

Sheet2

What I want to do is to VLOOKUP read the value of field B1 on sheet2 and to put that value as plain number in lookup_value part of the function.

So when I type in B3 (sheet2) number 3, function should automaticly change value as next:

=VLOOKUP(Sheet1!A3;Sheet1!A1:B6;2;FALSE)

When I type number 6, it should change to:

=VLOOKUP(Sheet1!A6;Sheet1!A1:B6;2;FALSE)

and bring the value from that row.

Literally B2 on sheet2 should play role of row# of cell in sheet1.

Solution

  • I found an aswer, I needed to call INDIRECT function.

    So function should be:

    =VLOOKUP(INDIRECT("Sheet1!A"&B1);Sheet1!A1:B6;2;FALSE)

    Maybe it will help somebody.

    BR Milos :)