two dimensional xlookup

Anonymous
2021-09-08T15:22:47+00:00

Hi,

Has anyone noticed that if doing a two way xlookup with non exact match the non exact must come second, even though thanks to index match I'm always putting vertical search first.

So; XLOOKUP(I2,B3:B20,XLOOKUP(J2,B3:G3,B3:G20,"N",-1)) Would not work I3 being the approximate -1 or 1 match.

But XLOOKUP(J2,B3:G3,XLOOKUP(I2,B3:B20,B3:G20,"N",-1)), does work.

Am I correct in this, or is it just me not seeing straight.

Richard.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-09-09T08:44:36+00:00

    Hi,

    It's simply does the 'non exact' match have to be the second xlookup.

    If both are exact i.e. 0 then it doesn't really matter whether horizontal or vertical searches come first but if a non exact match is sought i.e. 1 or -1 , then the order does matter.

    Richard.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102.2K Reputation points Volunteer Moderator
    2021-09-08T23:14:16+00:00

    Hi,

    Your question is not clear. Please share some data, explain the question and show the expected result.

    Was this answer helpful?

    0 comments