How can I VLOOKUP one row beneath my value?

I currently have a VLOOKUP formula which will look up the contents of cell A3, in columns C to AP and return the value of AP (see as below):

=VLOOKUP(A3,$C:$AP,40,FALSE)


However, the value I want it to return is one cell below this, in column AP. I've tried looking up various formulas online which use things such as OFFSET and MATCH but I've not used these before and I'm not sure how they'd fit in? Can someone please explain/help with this?

Try it this way:

=INDEX($C:$AP,MATCH(A3,$C:$C,0)+1,40)

Hope this helps.

Pete

Both of these seem to work great, and they make sense too so I think I'll be able to follow this in future.. Many thanks!!

You're welcome - thanks for the rep.

The main difference is that my formula uses the whole of the table, and gets data from the 40th column (hence the 40 at the end), whereas the other formula specifically gets data from column AP. With mine you can make the 40 variable, so that you could copy it across, for example, to get data from other columns - you would need to change A3 to $A3.

Pete