xlookup value in lookup array of 3 different columns (in separate workbook)

[memerich]

I am trying to use xlookup formula in "Recruit-Tracking" workbook to lookup a value (a unique email address in column G) within that workbook and look for that value which could be contained in any of 3 columns (Email (G), Alt Email (H) or Alt Email2 (I) in a different workbook calls "LEADS MASTER". If found, return the value under the "Lead Source" (C) of LEADS workbook into "Recruit Tracking" (also column C). If not found, return "Not on LEADS".

Here is the formula that works ONLY IF the email address is found in the primary email column (G).
=XLOOKUP([@Email],'[LEADS MASTER.xlsx]LEADS MASTER'!$G:$G,'[LEADS MASTER.xlsx]LEADS MASTER'!$C:$C,"Not on LEADS").

Not sure if I need to use multiple xlookups to handle each column I am attemtping to look it up from, as I cannot find syntax to say "look for it in Column "G, "H", OR "I"

I apologize but my spreadsheet contains sensitive data, so I am unable to post the data without recreating it first.

Thanks to all.

 

[RasGhul]

maybe you could use this? Note this only works when there is only 1 instance of a search value in any of the 3 columns.

Leads Master.xlsx
	C				G	H	I
1	Lead Source				Email	Alt Email	Alt Email2
2	Value1				123456	456789	789456
3	Value2				123457	456790	789457
4	Value3				123458	456791	789458
5	Value4				123459	456792	789459
6	Value5				123460	456793	789460
7	Value6				123461	456794	789461
8	Value7				123462	456795	789462
9	Value8				123463	456796	789463
10	Value9				123464	456797	789464
11	Value10				123465	456798	789465
LEADS MASTER

Recruit-Tracking.xlsx
	B	C
1	Email	Column1
2	456789	Value1
3	456790	Value2
4	456791	Value3
5	456792	Value4
6	88888	Not on LEADS
7	123461	Value6
8	123462	Value7
9	789458	Value3
10	789459	Value4
11	789460	Value5
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=IF(SUMPRODUCT(--('Leads Master.xlsx'!$G$2:$I$11=[@Email]))=0,"Not on LEADS",INDEX('Leads Master.xlsx'!$C$2:$C$11,SUMPRODUCT((--('Leads Master.xlsx'!$G$2:$I$11=[@Email]))*(ROW('Leads Master.xlsx'!$C$2:$C$11)-ROW('Leads Master.xlsx'!$C$2)))+1))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=filter('[LEADS MASTER.xlsx]LEADS MASTER'!$C:$C,('[LEADS MASTER.xlsx]LEADS MASTER'!$G:$G=[@Email])+('[LEADS MASTER.xlsx]LEADS MASTER'!$H:$H=[@Email])+('[LEADS MASTER.xlsx]LEADS MASTER'!$I:$I=[@Email]),"Not on LEADS")

 

[memerich]

Hi & welcome to MrExcel.
How about

=filter('[LEADS MASTER.xlsx]LEADS MASTER'!$C:$C,('[LEADS MASTER.xlsx]LEADS MASTER'!$G:$G=[@Email])+('[LEADS MASTER.xlsx]LEADS MASTER'!$H:$H=[@Email])+('[LEADS MASTER.xlsx]LEADS MASTER'!$I:$I=[@Email]),"Not on LEADS")

This works awesome! Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.