How to shift the result of the VLOOKUP function up or down one line.(Microsoft Excel)
As this cannot be achieved with the VLOOKUP function, a similar result is obtained with another function.
Contents
Formula
XLOOKUP function
The XLOOKUP function has a different argument specification, which makes it feasible.
Specify the get range by shifting one line, as in the example.
=XLOOKUP(Search value, Search range, Get range)If the range of get items is shifted down one line, the result is also shifted down one line.
=XLOOKUP(A2,D2:D5,E3:E6)
If the range of get items is shifted up one line, the result is also shifted up one line.
=XLOOKUP(A2,D2:D5,E1:E4)
It does not matter if you shift more than one line.
INDEX and MATCH functions
For the INDEX and MATCH functions, the formulas are a little more complex.
This is mainly used in versions where the XLOOKUP function cannot be used.
=INDEX(Get range, MATCH( Search value, Search range,0) + Number of rows to shift)
If it is to be shifted down, the formula is as follows.
=INDEX(E2:E5,MATCH(A2,D2:D5,0) + 1)
If it is to be shifted up, the formula is as follows.
=INDEX(E2:E5,MATCH(A2,D2:D5,0) - 1)
---