XLOOKUP - but find all matches ...

[adambc]

I have an XLOOKUP that reads like this ...

=IFERROR(XLOOKUP(B1 & B2,'SharePointpathname/[WBname.xlsx]Sheet1'!$J:$J & 'SharePointpathname/[WBname.xlsx]Sheet1'!$K:$K,'SharePointpathname/[WBname.xlsx]Sheet1''!$A:$A),"")

... where B1 (Firstname) & B2 (Surname) are entered via two dialogue boxes

... and Firstname is in 'SharePointpathname/[WBname.xlsx]Sheet1'!$J:$J) & Surname is in ('SharePointpathname/[WBname.xlsx]Sheet1'!$K:$K)

... and 'SharePointpathname/[WBname.xlsx]Sheet1'!$A:$A = ID Number which is what I want

But I may have multiple matches so can't use XLOOKUP?

Is there an alternative that will return ALL matches?

And all the better if it's a VBA solution rather than an Excel function

Thanks ...

 

[MARK858]

Is there an alternative that will return ALL matches?

Have a look at using the Filter function

XLOOKUP Formula To Return All Matches

If you are trying to figure out how to get Excel’s XLOOKUP function to return more than one result, you’ve come to the right place! Is this even possible? You will learn all about your options in this article.

www.thespreadsheetguru.com

 

[Joe4]

It looks like you are using Excel 365. In 365, there is a new FILTER function that can do stuff like this.
See here for more details: FILTER function - Microsoft Support

 

[RobP]

Hi,

I guess you can use FILTER() instead like this :

=IFERROR(FILTER($A:$A,($J:$J=B1)*($K:$K=B2)),"")

(adding your sharepoint details before it as you have above..)

 

[Joe4]

Hi,

I guess you can use FILTER() instead like this :

=IFERROR(FILTER($A:$A,($J:$J=B1)*($K:$K=B2)),"")

(adding your sharepoint details before it as you have above..)

What is the IFERROR formula needed for?
The FILTER function already has an argument of what to return if there is no match (3rd argument), i.e.

=FILTER($A:$A,($J:$J=B1)*($K:$K=B2),"")

 

[RobP]

evidently nothing Joe good spot.

 

[Joe4]

evidently nothing Joe good spot.

I must admit, the main reason I know that is because I did the same thing when I first starting using the function (and was unaware of that third argument too!).

 

[RobP]

well, I can't say I didn't know.. but was more just paying too much attention to the OP's use of the Xlookup ...

 

[adambc]

What is the IFERROR formula needed for?
The FILTER function already has an argument of what to return if there is no match (3rd argument), i.e.

=FILTER($A:$A,($J:$J=B1)*($K:$K=B2),"")

Perfect, thanks to all for your help ...