Formula Help

[catherine9910]

I was trying to use XLOOKUP formula, but it is not working, and I think I know why, I just need help with finding the right formula for the job. Attached is an example workbook with my question. Thanks in advance.

[#2]

@catherine9910 

=IF(NOT(ISNA(VLOOKUP(A2,CHOOSE({1,2},'Manager Groups'!$A$2:$A$3,'Manager Groups'!$B$1),2,0))),VLOOKUP(A2,CHOOSE({1,2},'Manager Groups'!$A$2:$A$3,'Manager Groups'!$B$1),2,0),IF(NOT(ISNA(VLOOKUP(A2,CHOOSE({1,2},'Manager Groups'!$D$2:$D$3,'Manager Groups'!$E$1),2,0))),VLOOKUP(A2,CHOOSE({1,2},'Manager Groups'!$D$2:$D$3,'Manager Groups'!$E$1),2,0),IF(NOT(ISNA(VLOOKUP(A2,CHOOSE({1,2},'Manager Groups'!$G$2:$G$3,'Manager Groups'!$H$1),2,0))),VLOOKUP(A2,CHOOSE({1,2},'Manager Groups'!$G$2:$G$3,'Manager Groups'!$H$1),2,0))))

Maybe this formula does what you want to do.

[#3]

@catherine9910 As a variant, in E2 and fill down:

=INDEX('Manager Groups'!$A$1:$H$1,,SUM(--('Manager Groups'!$A$2:$H$3=A2)*COLUMN('Manager Groups'!$B$1:$I$1)))