# XLOOKUP Formula Not Able To Use Correctly

#### MB028

##### New Member
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!

#### Attachments

• 11.8 KB Views: 3

#### shrivallabha

##### Excel Ninja
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

##### New Member
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

##### New Member
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

#### Attachments

• 653.8 KB Views: 4

#### shrivallabha

##### Excel Ninja
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

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

Regards,
Manoj