Xlookup and return value not from the same row-CodePudding

Is it possible to use XLOOKUP lookup and return value that are not on the same row?

Table 1 is the dataset that I'm trying use to look up from Table 2.

In Table 1 colA value ="1" is the Parent, and "2" is the Child. And, the parent row containing the address.

example 1 Lookup Value Table2.A3 Lookup Array Table1.colB Return Array Table1.C3 & D3

example 2 Lookup Value Table2.A4 Lookup Array Table1.colB Return Array Table1.C5 & D5

CodePudding user response：

Instead of using XLOOKUP(), use INDEX(MATCH()) and increment or decrement the row by 1 (or even a second MATCH() to find the amount it should change by, though this would be pretty gross)

INDEX ( <return array> , MATCH ( <lookup value> , <lookup array> , 0)   1)

example 2
Assuming a lookup from Cell B3 in Sheet2 to example 1 Sheet1

=INDEX(Sheet1!C3:C:10000, MATCH(A3, Sheet1!B3:B10000, 0) - 1)

CodePudding user response：

Just offset your return_array parameter.

=XLOOKUP(A3,$F$3:$F$7,$G$2:$H$6)