Combining two Xlookup function

[ammykhan]

Hey All friends,

Can I get suggestion how to combine two lookup functions, I have a situation where I have to lookup customer data using their employee id but some customer forgets to give their details in such a scenario I need to filter the data by making use of their name instead. Want to make a combined formula to filter data by using either their Name or Employee Id, is there a way to combine two lookup functions for keeping our Excel sheet simple. The formula I'm trying to build shall be more or less like the one below.

=OR(XLOOKUP($D2,Staff_no, Location), XLOOKUP($D2,Name, Location)

Any advice from my senior fellows will be highly appreciated so I can further my insight in MS Excel, thanks in advance

 

[Peter_SSs]

is there a way to combine two lookup functions for keeping our Excel sheet simple.

Possibly, but we would need to see what your Excel sheet looks like. Can you give us some smallish (10-20 rows) of dummy but representative sample data and the expected results with XL2BB so that we can see what data you are dealing with and what results you would want from it?

 

[ammykhan]

Thanks Sir, If we find a way out, that will make my life quite easy. My excel sheet is structured like this.

https://docs.google.com/spreadsheets/d/1dajeiWXov0IpmtME6D4ktxVOu0Lv2O0V/edit?usp=drivesdk&ouid=108986451146549243648&rtpof=true&sd=true

 

[Alex Blakenburg]

Filter is quite different to Xlookup. Xlookup will return a single result while Filter can return multiple results.
If you just wanted a single result for both you could make use of the if not found position.
=xlookup( lookup_value, lookup_array, return_array, if_not_found)
eg =XLOOKUP($D2, Staff_no, Location, XLOOKUP($D2,Name, Location))
You could also put Filter in that position but you would need room for it to Spill.

PS: You did not make your link available to anyone with the link and we can't access it,

 

[ammykhan]

Filter is quite different to Xlookup. Xlookup will return a single result while Filter can return multiple results.
If you just wanted a single result for both you could make use of the if not found position.
=xlookup( lookup_value, lookup_array, return_array, if_not_found)
eg =XLOOKUP($D2, Staff_no, Location, XLOOKUP($D2,Name, Location))
You could also put Filter in that position but you would need room for it to Spill.

PS: You did not make your link available to anyone with the link and we can't access it,

Yup, you were right I had to change the accessibility option in drive, the excel sheet I have is very simple one, I have four columns in the sheet i.e Staff ID, Name, Mailbox Number & Location but the sheet I have is composed of 20k rows to say filter I meant to extract the meaningful information from the dataset based on either Staff ID or name, by using the 'filter' word I didn't mean at all to ADD this function into my formula

 

[Peter_SSs]

My excel sheet is structured like this.

Your Excel sheet does not include this ..

and the expected results ... so that we can see ... what results you would want from it?

 

[ammykhan]

Your Excel sheet does not include this ..

Yes off course I didn't include it because I don't know how to do it, as I mentioned in the query that I'm trying to combine two XLOOKUP functions, it will be quite confusing / cumbersome for me to make two separate worksheets for filtering the same data based on Staff Id / Name, currently if a customer forgets to give their employee id, I simply use CTRL+F which isn't a good way to find data for large number of people. So still I'm able to carry on my work despite my limited experience in tech but my eagerness to become an excel-pro led me to seek your help on this forum.

 

[Peter_SSs]

Yes off course I didn't include it because I don't know how to do it,

Enter some results of each type manually. Otherwise we don't know what you are aiming for.

 

[ammykhan]

I tried to explain the problem from my side quite well but probably I didn't convey my msg to you properly,sorry for wasting your time on this.. So, I enter the data now for both type, trying to combine them into one, I don't know if we can do that or not, pls check it.

my_checklist01.xlsx

Main Sheet Staff No,Name,Mail,Location 259532,SAMEH SHABAN,4251,DSO 268845,MATTHANA PISITBUT,4470,GHD 295743,NOOR WAHEDAH,5270,EGHQ 377128,DUANGRUTHAI THAMMAPHANYA,6930,Sarab 377133,BOMI KIM,6998,MS 378234,JI HYUN,7572,MS 378311,MARIANA NIKOLAEVA,7546,TCOM 378485,JOANNE MIRIAM,7693,OBD 379137,IL...

docs.google.com

Thanks a lot for the motivation,

 

[Peter_SSs]

Something like this then?

my_checklist01 (1).xlsx
	A	B	C	D
1	Location	Mailbox	Name	ID
2	DSO	4251		259532
3	DSO	4928	IL SUK
4	MS	7572	JI Hyun
5	GHD	4470		268845
6	OBD	7693		378485
7	GHD	10267	jin lee
8	Sarab	11808	na rae
CC

Cell Formulas
Range	Formula
A2:A8	A2	=XLOOKUP(D2,Staff_No,Location,XLOOKUP(C2,Name,Location))
B2:B8	B2	=XLOOKUP(D2,Staff_No,Mail,XLOOKUP(C2,Name,Mail))

Named Ranges
Name	Refers To	Cells
Location	='Main Sheet'!$D$2:$D$20	A2:A8
Mail	='Main Sheet'!$C$2:$C$20	B2:B8
Name	='Main Sheet'!$B$2:$B$20	A2:B8
Staff_No	='Main Sheet'!$A$2:$A$20	A2:B8