Search code examples
matchexcel-2007vlookup

Dynamic reference in vlookup

I have a formula like this:

=VLOOKUP(A14,Cars!C25:S49,17,0)

I would like to know if the below is possible:

The numbers 25, 49 which are specified in the Range of the above vlookup were present in different cells. I.e.,

C3 = MATCH(.....) --> Match results in the number 25
C4 = MATCH(.....) --> Match results in the number 49

I tried something like this:

=VLOOKUP(A14,Cars!C&C3:S&C4,17,0)

But this shows the error. Can someone suggest me how to achieve this?

Solution

  • An alternative to the volatile Indirect() can be achieved with Index along the lines of

    =vlookup(A14,Index($C:$C,$C$3):Index($S:$S,$C$4),17,false)