Oct 18 2022 06:26 PM
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)
Oct 18 2022 09:53 PM
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".
Oct 18 2022 09:59 PM
Oct 18 2022 09:53 PM
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".