Search code examples
excelgoogle-sheetsxlookupindex-match

XLOOKUP function if columns misplaced

I have two sheets, Both Sheet1 & Sheet2 consists of 6 columns which are:

  • Shop Name
  • Shop status
  • Business Type
  • City
  • Operating hours
  • Delivery model

The below INDEX MATCH Function finds shop name in Sheet2 and returns exact values of shop status against each Shop name:

=INDEX(Sheet2!$A$1:$H,MATCH($A2,Sheet2!$A$2:$A,0),MATCH(B$1,Sheet2!$A$1:$H$1,0))

which also can locate Shop status values if it's misplaced in Sheet2, I need that Xlookup function to the same as the above INDEX MATCH function =XLOOKUP($A2:A,Sheet2!$A$2:$A,Sheet2!$B$2:$B).

Please find the attached link where it shows an example of the above: In Sheet1 Cell B2, INDEX MATCH function still returns exact match even though I changed data in Sheet2 to be in column D, however XLOOKUP in B3 returns nothing as still search for date in column B in Sheet2.

Solution

  • You'll need to use a simpler INDEX-MATCH inside XLOOKUP to find the correct column:

    =XLOOKUP($A2,Sheet2!$A$2:$A,INDEX (Sheet2!$B$2:$H,,MATCH(B$1,Sheet2!$B$1:$H$1,0)))

    PS: it's not clear why you need to transform it into a XLOOKUP