Forum Discussion

catherine9910's avatar
catherine9910
Brass Contributor
Apr 12, 2022

Formula Help

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.

Formulas and Functions

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 12, 2022

    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)))

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 12, 2022

    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.

Resources