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

 

[ammykhan]

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

Hi Sir, Please your insight helped make get my desired result with minimal fuss but now we are regularly updating our Main Sheet , my actual sheet is like Screenshot(1) without any error but after updating the data I'm getting a '0' error and the column NNN isn't returning anything unless in the adjacent column I have typed something as can be seen in Screenshot(2), pls can you suggest me what I can do while updating my workbook, thanks a lot for being helpful.