Dynamic vlookup

Hi everyone,

I'm trying to create a dynamic ranged vlookup, but can't seem to do it. This is the situation: I want the vlookup to start only from a certain row, where the row that I want to start looking in changes, but the final element of the range is always the same.

I've attached a sample file with the relevant information. Row A contains the row number I would like to start looking in, and row G is the array in which I would like to search, where I would like to search only from the row number in column A through to the end of the array in G. For example, A2 contains "20", so I would like to do a vlookup that looks for the value in B2 in the array in G starting from row 20 through to row 23 (the final row in the array). Similarly for the remaining values in column B



The formula I'm trying to use is the following: =VLOOKUP(B2,G&ROW(OFFSET(G2,A2,0))&G23,1,FALSE). So what I'm trying to do here is look for the value of B2 in an array which is offset by the number in row A2.

Any ideas would be awesome!

Thanks

Hi, try

Please Login or Register  to view this content.

Worked beautifully!
Thanks!

The non-volatile option would be:
=VLOOKUP(B2,INDEX(INDEX(G:G,A2):G23,0),1,0)