Search code examples
excelworksheet-function

Index Match Offset to lookup a value and return Offset criteria

I have a worksheet of 300 invoices, each exactly the same format, in A:H. Column A contains the ID that will serve as the reference value, spaced 50 cells apart. If I know the reference ID, I use this formula:

=OFFSET($A$302,20,7)

In another workbook, I have a small list of IDs and I need to match that ID in Column A, then return the value 20 rows down and 7 columns across. I tried that with VLookup, but it didn't work:

=VLOOKUP(L302,$A:$H,OFFSET(L302,20,7),FALSE)

Where L302 is the lookup value (I did this within the same worksheet to test). Any suggestions?

Solution

  • Use INDEX/MATCH:

    INDEX($H:$H,MATCH(L302,$A:$A,0)+20)