VLOOKUP is a useful Excel formula, that allows you to collect data from a complex database and deliver it where needed. When utilized appropriately, it can save you a lot of time while also improving your Excel efficiency and proficiency. This article will guide you how to use and perform VLOOKUP with multiple criteria. Continue reading !
How to Use VLOOKUP With Multiple Criteria?
VLOOKUP with multiple criteria is not directly supported, but you can get a comparable result by combining INDEX, MATCH, and SUMIFS. Here's a general approach to simulate a VLOOKUP with multiple criteria:
- Use INDEX and MATCH: Instead of VLOOKUP, use INDEX and MATCH functions together. INDEX returns the value of a cell in a specified row and column of a table, while MATCH returns the relative position of an item in a range.
- Create Helper Columns: You should create helper columns to concatenate or evaluate your multiple criteria. For instance, if you have criteria in columns A and B, you can create a helper column that combines both criteria into one.
- Combine MATCH with Multiple Criteria: Use the MATCH function to find the position of your desired value based on multiple criteria. Furthermore, you'll need to use array formulas or other techniques to handle multiple criteria effectively.
- SUMIFS for Numeric Results: If you're looking to sum values based on multiple criteria, consider using the SUMIFS function. Additionally, it allows you to sum values that meet multiple conditions.
Here's an example of how you might use these functions together:
Suppose you have a table with data in columns A, B, and C. You want to retrieve a value from column C based on criteria in columns A and B.
- In column D, concatenate the criteria from columns A and B into one helper column.
- Use the INDEX function to retrieve the value from column C based on the position returned by the MATCH function that matches the concatenated criteria.
Here's the formula you can use:
=INDEX(C:C, MATCH("criteria1" & "criteria2", A:A & B:B, 0))
Here, you can replace "criteria1" and "criteria2" with the actual criteria you're searching for.
How to VLOOKUP with Multiple Criteria – Using a Helper Column
Using a helper column is a common and effective method to perform a VLOOKUP with multiple criteria. Here's how you can do it step by step:
- Concatenate Criteria: Create a helper column where you concatenate the criteria from the columns you want to match against. This creates a unique identifier for each row based on multiple criteria.
- Perform VLOOKUP: Use the concatenated criteria as the lookup value in your VLOOKUP formula.
Here is an example:
Suppose you have a table with data in columns A, B, and C. You want to retrieve a value from column C based on criteria in columns A and B.
- In column D, concatenate the criteria from columns A and B into one helper column. Assuming your data starts from row 2, you can use the formula =A2&B2 in cell D2 and drag it down to apply to all rows.
- Now, use VLOOKUP with the concatenated criteria as the lookup value.
=VLOOKUP("criteria1" & "criteria2", D: C, 2, FALSE)
Here you need to replace "criteria1" and "criteria2" with the actual criteria you're searching for.
This formula tells Excel to look for the concatenated criteria in the helper column (column D) and return the corresponding value from column C.
Note: This method is best for relatively small datasets.
How to VLOOKUP with Multiple Criteria – Using the CHOOSE Function
Using the CHOOSE function along with VLOOKUP can be an alternative method for performing a lookup with multiple criteria. Here's how you can do it:
Suppose you have a dataset with multiple criteria in columns A and B, and you want to retrieve data from column C based on these criteria.
- Create a Unique Identifier: Combine the criteria from columns A and B into a unique identifier. You can use CONCATENATE or the ampersand (&) operator to combine them.
- Use CHOOSE Function: The CHOOSE function can be used to select which column to return the result from, based on the concatenated criteria. The position of the result column in the CHOOSE function would depend on the combination of criteria.
Here's how you can set it up:
Assuming your data starts in row 2 and your concatenated criteria are in column D:
1. Create a unique identifier in column D by concatenating the criteria from columns A and B. For example, in cell D2, you can use the formula:
=A2 & "-" & B2
Drag this formula down to apply it to all rows. 2. Now, use the CHOOSE function along with VLOOKUP to select the appropriate column based on the concatenated criteria.
=VLOOKUP("criteria1-criteria2", A:D, CHOOSE(MATCH("criteria1-criteria2", D:D, 0), 3, 4), FALSE)
Replace "criteria1" and "criteria2" with your actual criteria.
- The MATCH function is used to find the position of the concatenated criteria in column D.
- CHOOSE function selects the appropriate column to return the result from (column C in this case). This formula looks for the concatenated criteria in column D and returns the result from column C.
How to Perform VLOOKUP for Multiple Criteria Using the Array Formula
Performing a VLOOKUP for multiple criteria using an array formula involves combining the VLOOKUP function with array processing. Here's how you can do it step by step:
Suppose you have a dataset with multiple criteria in columns A and B, and you want to retrieve data from column C based on these criteria.
- Create a Unique Identifier: Combine the criteria from columns A and B into a unique identifier. You can use CONCATENATE or the ampersand (&) operator to combine them.
- Use VLOOKUP with Array Formula: Instead of a single lookup value, you'll use an array of concatenated criteria. This array will be processed by the VLOOKUP function to find matches. Here's how you can set it up:
Assuming your data starts in row 2 and your concatenated criteria are in column D:
1. Create a unique identifier in column D by concatenating the criteria from columns A and B. For example, in cell D2, you can use the formula:
=A2 & "-" & B2
Drag this formula down to apply it to all rows. 2. Now, use VLOOKUP with an array formula to search for multiple criteria:
=VLOOKUP(TRUE, (A:A="criteria1")*(B:B="criteria2")*(C:C), 3, FALSE)
Replace "criteria1" and "criteria2" with the actual criteria you're searching for.
- (A: A="criteria1") and (B: B="criteria2") generate arrays of TRUE and FALSE values, where TRUE corresponds to rows that match the criteria.
- The multiplication (*) operator is used to create a combined array where only rows that meet both criteria are TRUE.
- This combined array is used as the lookup value for VLOOKUP.
- VLOOKUP returns the value from column C for the first match found.
Press Ctrl + Shift + Enter instead of just Enter to enter the formula as an array formula. This tells Excel to treat it as an array formula and process the arrays properly.
VLOOKUP Multiple Criteria Syntax
The VLOOKUP syntax is:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Where,
- Lookup_value: The reference value can be text, a numerical string, or a cell with the value you want to reference.
- Table_array: The overall data table. As such, the reference value you're seeking should be in the first column i.e., column 1 of this table, allowing Excel to proceed to the right and look for the return value.
- Col_index_num: The column number in which the return value is located. This value begins at 1 and increases proportionally to the number of columns in your table.
- Range_lookup: The fourth value is in brackets since it is not required to make this function work. Brackets in Excel syntax indicate that a parameter is optional. If you do not finish this value, Excel will select TRUE (or 1), indicating that you are looking for an approximation rather than an exact match to your reference value. TRUE is not a recommended value for text returns.
How to perform VLOOKUP with multiple criteria in Google Sheets
VLOOKUP is an advanced Google Sheets function that allows users to search for specific values in a table and retrieve the equivalent data from another column. To utilize VLOOKUP with multiple criteria, users must first organize their data such that it includes all of the criteria they want to search for. This can be accomplished by sorting the data into columns and rows, with the criteria provided in distinct columns. Then, by combining the VLOOKUP function with the "ARRAY FORMULA" function, users can enter numerous criteria to find and obtain the desired data. This allows users to efficiently and accurately search for specific information in a big dataset of Google Sheets.
Method 1: Use a helper column
VLOOKUP does not support native searches with multiple criteria in Google Sheets. Use VLOOKUP with a helper column to combine various criteria.
Method 2: Use FILTER
VLOOKUP does not support native searches with multiple criteria in Google Sheets. Use FILTER instead.
How VLOOKUP Multiple Criteria is better than XLOOKUP Multiple Criteria?
VLOOKUP multiple criteria requires the user to specify the whole table_array, searching only for the lookup value in the leftmost column. The column number in the following input determines which column will be used to return a value. In contrast, XLOOKUP multiple criteria lets you specify the lookup and return columns separately.
How to Move Data From One Excel Sheet to Another
Moving data from one Excel worksheet to another is as simple as entering the address of the data table into the calculation correctly. You can download the Mcrosoft Excel from App Store. When executing VLOOKUPs from one worksheet to another in the same workbook, you can enter the data table's address into the calculation using one of the following options:
- When writing the formula, pick both the worksheet that contains the data table in it and also the data table. Further, excel automatically enters the correct address for the data table into the formula.
- Enter the location of the data table directly into the calculation, using the format 'Name of Sheet'!(Data Table). For example, to have Excel look up a data table in cells A1:C10 from the worksheet called VLOOKUP, type 'VLOOKUP'!A1:C10. The single quotes around the worksheet name and the exclamation point indicate Excel to draw the table from another sheet.
FAQs on VLOOKUP with Multiple Criteria
How do I do a VLOOKUP with multiple criteria?
Follow the instructions below to use VLOOKUP multiple values:
- Create a special helper column on the left side of the table.
- Type your starting formula in the designated cell.
- Then, include the multiple search values.
- Input the table array.
- Finally, select a range lookup option.
Can you do a VLOOKUP with two lookup values?
Can you perform a VLOOKUP with two lookup values? Because two lookup values cannot be specified in a VLOOKUP formula, we must employ a workaround that consists of two steps: Step 1: Make a new column where we will construct unique lookup_values by merging our two lookup criteria - name and country - for example, "MellaThailand", "MellaNigeria", etc.
How can I use two VLOOKUPs in one formula?
All you have to do is nest the first VLOOKUP formula into the input of the second VLOOKUP formula. Here, the inner VLOOKUP searches for the book's name and returns the ID. The outer VLOOKUP looks for the book's ID and retrieves the price from Table 2.
How do I do a VLOOKUP with multiple criteria index and match?
To do an INDEX MATCH with multiple criteria in Excel, just use an ampersand (&) to place several references in the lookup value and lookup array inputs in the MATCH formula.
Can you do a VLOOKUP with two criteria in Google Sheets?
VLOOKUP cannot be used directly for multiple criteria. Instead, build a new helper column and use VLOOKUP on various criteria to combine multiple existing columns. By combining First Name and Last Name with "&", you may create a Helper column.
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP vs. VLOOKUP: Key Differences XLOOKUP can search for values to the left and right of the lookup array, whereas VLOOKUP can only seek values to the right of the lookup value column. When a valid match is not found, XLOOKUP allows you to personalize the text, whereas VLOOKUP displays a #N/A (error sign).
How do you use an IF THEN formula in Excel?
IF function | Microsoft Support The IF function, a logical function, returns one result if a condition is true and another if it is false. For example: =IF(A2>B2, "Over Budget","OK") =IF(A2=B2, B4-A4,"")
Read More Blogs: Spill Error Excel