Standard VLOOKUP can only search for a value based on a single criterion. This guide will show you a simple and effective method to perform a lookup using multiple criteria by creating a helper column.
In your data table, insert a new column to the left of your data. This column will be used to combine your criteria. For example, if your data starts in column B, insert a new column A.
In the first cell of your new helper column (e.g., A2), create a formula that concatenates the cells containing your criteria. For instance, if you want to look up based on columns B and C, your formula would be =B2&C2. Drag this formula down for all rows.
Now, you can write your VLOOKUP formula. For the lookup_value, combine the criteria you're searching for in the same way (e.g., G2&H2, where G2 and H2 are your lookup cells).
For the table_array, select the entire data range, starting with your new helper column. Adjust the col_index_num to account for the added column. For example, if you want to return a value from the original column D (now column E), the index number would be 5. Your final formula might look like: =VLOOKUP(G2&H2, A:E, 5, FALSE).
Pro Tips for Multiple Criteria Lookups
If you have Microsoft 365, the XLOOKUP function can handle multiple criteria natively without a helper column, making it a more modern and efficient solution. Example: =XLOOKUP(1, (B2:B10=G2)*(C2:C10=H2), D2:D10).
When combining criteria, use a unique separator (e.g., =B2&"|"&C2) to prevent incorrect matches. For example, 'AB' and 'C' would look the same as 'A' and 'BC' without a separator.
The combination of INDEX and MATCH is a powerful and flexible alternative to VLOOKUP that can easily handle multiple criteria and is more robust than VLOOKUP.
Always use FALSE or 0 as the last argument in your VLOOKUP formula to ensure you are looking for an exact match, which is crucial for multi-criteria lookups.
Explore other Excel guides and shortcuts