0

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.

Barnet Latoya
  • 33
  • 4
  • Lets start with this: [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – David Leal Jun 24 '23 at 00:22
  • 2
    I am with @DavidLeal here, please provide a sample sheet based on the reference link provided in the comment. – Babanana Jun 24 '23 at 01:19
  • Hello @Babanana, I attached an example with published sheet, you can find link in the post after editing. – Barnet Latoya Jun 24 '23 at 01:37
  • @BarnetLatoya Are you looking for something like this? `=XLOOKUP($A2,Sheet2!$A$2:A,Sheet2!$D$2:D)` I have test it and should show you the same result as you current formula. Please let me know so I can post this as an answer and let the community find this post to help them on their projects as well. If it is not please let me know so I can have a better understanding of your desired result – Babanana Jun 24 '23 at 01:57

1 Answers1

3

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

Martín
  • 7,849
  • 2
  • 3
  • 13
  • Thanks Martin, the reason to change to XLOOKUP is that I couldn't use INDEX-MATCH with ARRAYFORMULA function, if you do have a resolution for that, please advise. – Barnet Latoya Jun 24 '23 at 08:47