Forum Discussion

rbalza's avatar
rbalza
Brass Contributor
Oct 19, 2022
Solved

XLookup Value multiple criteria

Hi everyone, I was wondering how to do a lookup value using multiple criteria. 

 

Scenario: I have 2 sheet tabs; one is for data entry and other is for overview (like a pivot table view). Whatever I enter on the "data entry tab" will show on a formatted overview tab. On overview, I have this XLOOKUP code that returns #N/A and I couldn't figure out what I have missed. Please find attached sheet. Thanks for the assistance in advance 🙂

 

 

=XLOOKUP(Overview!A4&Overview!B4&Overview!C1,'Data Entry'!B1&'Data Entry'!B2&'Data Entry'!C2,'Data Entry'!A2)

 

 

 

 

excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Oct 19, 2022

    rbalza Not really sure what you have in mind with this formula but to avoid the error you need to make sure that the elements in the look_up array are in the correct order.

    In you original formula, you were trying to match 44841ProcessingIG with ProcessingIG44841

    Hence, #N/A.

    =XLOOKUP(Overview!A4&Overview!B4&Overview!C1,'Data Entry'!C2&'Data Entry'!B1&'Data Entry'!B2,'Data Entry'!A2)

     This will return "Apple".  

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 19, 2022

    rbalza Not really sure what you have in mind with this formula but to avoid the error you need to make sure that the elements in the look_up array are in the correct order.

    In you original formula, you were trying to match 44841ProcessingIG with ProcessingIG44841

    Hence, #N/A.

    =XLOOKUP(Overview!A4&Overview!B4&Overview!C1,'Data Entry'!C2&'Data Entry'!B1&'Data Entry'!B2,'Data Entry'!A2)

     This will return "Apple".  

Resources