XLOOKUP Formula Not Able To Use Correctly

[MB028]

Hi, I am not able to use the correct XLOOKUP formula in the attached file.

Question: For a Business Unit, there can be multiple Cost Center Levels. Now, I've a list of these Cost Centers combined and need to find out to which Business Unit they belong to?

Thanks for the help!

 

[shrivallabha]

You can probably try below formula and see if it helps:
=XLOOKUP(B10,$B$2:$B$5&$C$2:$C$5&$D$2:$D$5,$A$2:$A$5,"",0,1)

 

[MB028]

You can probably try below formula and see if it helps:
=XLOOKUP(B10,$B$2:$B$5&$C$2:$C$5&$D$2:$D$5,$A$2:$A$5,"",0,1)

Thank you very much Shrivallabha. It works, really appreciated.
Regards,
Manoj

 

[MB028]

Hi Shrivallabha,

When I'm trying to use this XLOOKUP formula on 2 tabs it's not working for me. I'm sure, I must be doing something wrong in the formula

Can you have a quick look at the attached excel/Tab: PTBS00-ACCT_CC/Cell:H2

 

[shrivallabha]

Hi Shrivallabha,

When I'm trying to use this XLOOKUP formula on 2 tabs it's not working for me. I'm sure, I must be doing something wrong in the formula

Can you have a quick look at the attached excel/Tab: PTBS00-ACCT_CC/Cell:H2

Your formula needs a small correction to match the ranges. See red marked change.

=XLOOKUP(H2,'Global Hierarchy-CEXTCE_SEP-20'!C$8:C$14320&'Global Hierarchy-CEXTCE_SEP-20'!D$8:D$14320&'Global Hierarchy-CEXTCE_SEP-20'!E$8:E$14320&'Global Hierarchy-CEXTCE_SEP-20'!F$8:F$14320&'Global Hierarchy-CEXTCE_SEP-20'!G$8:G$14320,'Global Hierarchy-CEXTCE_SEP-20'!B$8:B$14320,"",0,1)

 

[MB028]

Thank you very much Shrivallabha. This works perfectly fine. Have a good day!

Regards,
Manoj