Help with XLOOKUP

[zinah]

Hi,

I have a below table that EEIDs and some of those EEID have multiple categories, what I need to do is to get only the EEID with Promotion category. The formula I have brings the first category of each EEID only, how can I edit the formula to let it bring only the Promotion category and if it's not Promotion then "Not Promotion"

Thanks!

XLOOKUP Help.xlsx
	A	B	C	D	E	F	G	H	I
1	EEID	Category						EEID	Promotion Status
2	AAA	Transfer						AAA	Transfer
3	BBB	Data Change						BBB	Data Change
4	CCC	Promotion						CCC	Promotion
5	DDD	Compensation Change Only						DDD	Compensation Change Only
6	AAA	Compensation Change Only						EEE	Promotion
7	CCC	Transfer						RRR	Promotion
8	DDD	Data Change
9	EEE	Promotion
10	CCC	Compensation Change Only
11	AAA	Transfer
12	DDD	Data Change
13	BBB	Promotion
14	EEE	Compensation Change Only
15	AAA	Promotion
16	RRR	Promotion
17
18
19
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=XLOOKUP(H2,$A$2:$A$16,$B$2:$B$16)

 

[johnny51981]

Give this a shot

=XLOOKUP(1,(A2:A16=H2)*(B2:B16="Promotion"),B2:B16,"")

 

[zinah]

Give this a shot

=XLOOKUP(1,(A2:A16=H2)*(B2:B16="Promotion"),B2:B16,"")

Thank you for your quick response! Do I need to edit the formula to be like this instead?

=XLOOKUP(1,($A$2:$A$16=H2)*($B$2:$B$16="Promotion"),$B$2:$B$16,"")

 

[MisterProzilla]

Do I understand you correctly: Using the top cells H2:I2 as an example, you want a formula which looks for a row with 'AAA' in column A and 'Promotion' in column B, then returns Promotion/No Promotion in I2 respectively?

It sounds like you'd want to use =IF(COUNTIFS):

=IF(COUNTIFS($A$2:$A$16,$H2,$B$2:$B$16,"Promotion"),"Promotion","No Promotion")

ie. If both desired values are found in both columns, then the countifs return True and the IF returns 'Promotion'; if not, it returns False and the IF returns 'No Promotion'.

 

[johnny51981]

Thank you for your quick response! Do I need to edit the formula to be like this instead?

=XLOOKUP(1,($A$2:$A$16=H2)*($B$2:$B$16="Promotion"),$B$2:$B$16,"")

Yeah. I got lazy with the absolute-ing.

 

[zinah]

Yeah. I got lazy with the absolute-ing.

Thank you SO MUCH! That worked perfectly well

 

[zinah]

Do I understand you correctly: Using the top cells H2:I2 as an example, you want a formula which looks for a row with 'AAA' in column A and 'Promotion' in column B, then returns Promotion/No Promotion in I2 respectively?

It sounds like you'd want to use =IF(COUNTIFS):

=IF(COUNTIFS($A$2:$A$16,$H2,$B$2:$B$16,"Promotion"),"Promotion","No Promotion")

ie. If both desired values are found in both columns, then the countifs return True and the IF returns 'Promotion'; if not, it returns False and the IF returns 'No Promotion'.

Yes, you did understand me correctly and your formula worked too, thank you so much for your help