How to shift the result of the VLOOKUP function up or down one line.(Microsoft Excel)

Japanese version.

As this cannot be achieved with the VLOOKUP function, a similar result is obtained with another function.

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)

---

Links

Tips. XLOOKUP & VLOOKUP & HLOOKUP(Microsoft Excel)