SOLVED

XLookup Value multiple criteria

Brass Contributor

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)

 

 

 

 

2 Replies
best response confirmed by rbalza (Brass Contributor)
Solution

@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".  

Hi @Riny_van_Eekelen, thanks very much for taking the time on this. I will accept your answer to my query as I posted another query which is clearer what is the desired result. You can follow up the topic here on this link :) Once again, thank you!
https://techcommunity.microsoft.com/t5/excel/dynamic-list-of-row-columns-based-on-table/m-p/3656403
1 best response

Accepted Solutions
best response confirmed by rbalza (Brass Contributor)
Solution

@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".  

View solution in original post