How to Use VLOOKUP with Multiple IF Statements in Excel [2 Examples]

VLOOKUP is a widely used Excel function designed for searching specific values within tables or ranges and extracting corresponding data. It’s a versatile tool that can be seamlessly integrated with other functions, including multiple IF functions. VLOOKUP in combination with multiple IF statements represents an advanced technique that leverages the robustness of VLOOKUP and the adaptability of IF statements. In this article, I will explore how to effectively employ the VLOOKUP function with multiple IF statements in Excel.

Example 1: Using Multiple IF Statements with VLOOKUP Function in Excel

Nested IF statements in Excel are used to perform multiple conditional checks and return different results based on those conditions. On the other hand, Microsoft Excel’s VLOOKUP function is an important tool for searching for a certain value in a table or range of data and returning a corresponding value from a different column in the same row.

Here, I will employ a combination of nested IF statements and VLOOKUP to precisely extract a value from a dataset. For example, I will determine the price of a mobile phone based on its specific quality.

Syntax

=IF(logical_test, VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), value_if_false)

Formula

=IF(G2="128 GB", VLOOKUP(G1, A2:D8, 2, FALSE),IF(G2="256 GB", VLOOKUP(G1, A2:D8, 3, FALSE),IF(G2="512 GB", VLOOKUP(G1, A2:D8, 4, FALSE),"didn't found")))

Formula Breakdown

• This formula combines the IF and VLOOKUP functions to search for a specific value based on the conditions set in cell G2. It begins by checking if G2 equals “128 GB”; if true, it uses VLOOKUP to find a corresponding value in column B of the dataset. If not, it proceeds to the next nested IF statement, checking for “256 GB,” and repeats the process with VLOOKUP.
• This pattern continues with “512 GB.” If none of these conditions match, it returns “didn’t find.” Essentially, this formula dynamically retrieves data from a dataset based on the value in G2 and G1, providing flexibility in data retrieval.

Follow the steps below to use multiple IF statements with the VLOOKUP function in Excel:

1. Choose cell G3.
2. Copy this formula: =IF(G2=”128 GB”, VLOOKUP(G1, A2:D8, 2, FALSE),IF(G2=”256 GB”, VLOOKUP(G1, A2:D8, 3, FALSE),IF(G2=”512 GB”, VLOOKUP(G1, A2:D8, 4, FALSE), “didn’t found”)))
3. To see the result, press ENTER.

Final Result

At last, I’ve located the precise result for the ‘Samsung S23’ with 512 GB RAM.

Example 2: Combining ISNUMBER and VLOOKUP Functions with Multiple IF Statements in Excel

In this scenario, I have employed a dataset featuring columns such as ‘Employee Name,‘ ‘Employee ID,’ ‘Department,’ and ‘Score.’ My objective is to ascertain the performance categorization (good, average, or excellent) of employees based on their respective scores. To achieve this, I have used the combined functionality of the ISNUMBER and IF functions in tandem with VLOOKUP.

Syntax

=IF(ISNUMBER(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), value_if_false))

Formula

=IF(ISNUMBER(VLOOKUP(G1, A2:D9, 4, FALSE)),IF(VLOOKUP(G1, A2:D9, 4, FALSE) >= 90, "Excellent",IF(VLOOKUP(G1, A2:D9, 4, FALSE) >= 80, "Good",IF(VLOOKUP(G1, A2:D9, 4, FALSE) >= 70, "Average","Below Average"))),"Employee not found")

Formula Breakdown

1. This formula is used to evaluate an employee’s performance based on their Employee ID (cell G1) within a dataset (range A2:D9). It starts by checking if the Employee ID exists in the dataset using ISNUMBER and VLOOKUP. If found, it further assesses the performance score in column D.
2. If the score is 90 or higher, it assigns an “Excellent” rating, between 80 and 89 results in a “Good” rating, between 70 and 79 results in an “Average” rating, and below 70 is labeled as “Below Average.”
3. If the Employee ID is not found, it returns “Employee not found.” In essence, this formula provides a performance rating for an employee based on their ID or indicates if the employee is not in the dataset.

To combine multiple IF statements with the VLOOKUP and ISNUMBER functions in Excel, follow the steps below:

1. Select cell G3.
2. Type This formula: =IF(ISNUMBER(VLOOKUP(G1, A2:D9, 4, FALSE)),IF(VLOOKUP(G1, A2:D9, 4, FALSE) >= 90, “Excellent”,IF(VLOOKUP(G1, A2:D9, 4, FALSE) >= 80, “Good”,IF(VLOOKUP(G1, A2:D9, 4, FALSE) >= 70, “Average”,”Below Average”))),”Employee not found”)
3.  Hit ENTER to see the result.

Final Result

After applying this formula, I discovered that Sarah’s performance is ‘Excellent,’ which aligns accurately with the dataset.

Using Multiple VLOOKUP Functions With Nested IFERROR Functions in Excel

Imagine you have an Excel sheet with three tables. The first table lists various mobile phone brands and their respective prices. The second table includes laptop brands and their prices, and the third table features smartwatch brands and their corresponding prices.

Now, if you want to search for a specific mobile phone or laptop, how can you efficiently achieve this? To find the exact result, you can employ a combination of multiple VLOOKUP functions along with the IFERROR function.

Syntax

=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]),  value_if_error)

Formula

=IFERROR(VLOOKUP(F10, B2:C7, 2, 0), IFERROR(VLOOKUP(F10, F2:G7, 2, 0), IFERROR(VLOOKUP(F10, J2:K7, 2, 0), "Can't find")))

Formula Breakdown

This formula searches for a value in cell F10 within three separate tables. It starts with the first table (B2:C7) and uses the VLOOKUP function to find the value. If it doesn’t find it, it moves to the second table (F2:G7), and then to the third table (J2:K7) if needed. If the value isn’t found in any of the tables, it displays “Can’t find.”

Using Multiple VLOOKUP Functions With Nested IFERROR Functions in Excel, follow the steps below:

1. Select cell G10.
2. Copy this formula: =IFERROR(VLOOKUP(F10, B2:C7, 2, 0), IFERROR(VLOOKUP(F10, F2:G7, 2, 0), IFERROR(VLOOKUP(F10, J2:K7, 2, 0), “Can’t find”)))
3. Press the ENTER button.

Final Result

After using this formula, I successfully obtained the precise result from Table 1.

Conclusion

In this article, I’ve discussed how to effectively utilize multiple IF functions with VLOOKUP, enhancing your ability to find desired values with precision. Additionally, I’ve explained how to employ multiple VLOOKUP or nested VLOOKUP functions in combination with the IFERROR function.

Can you do a VLOOKUP with multiple conditions?

Yes, you can perform a VLOOKUP with multiple conditions in Excel by combining it with functions like INDEX, MATCH, or array formulas.

Use the IF and ISNUMBER functions to set conditions and check their fulfillment before applying the VLOOKUP.

Another approach involves combining INDEX and MATCH for a more flexible lookup. Additionally, array formulas can be used to handle multiple conditions simultaneously.

Customize the formulas based on your specific data and criteria for an efficient multiple-condition VLOOKUP operation.

How do I combine if statements with VLOOKUP?

To combine IF statements with VLOOKUP in Excel, use the IF function to set conditions and embed the VLOOKUP within it.

Example: =IF(condition, VLOOKUP(…), “”)

How do you do a VLOOKUP if there are multiple results?

If there are multiple results in VLOOKUP, use INDEX and MATCH functions or array formulas for more advanced and accurate data retrieval in Excel.

Use INDEX and MATCH:

1. Combine the INDEX and MATCH functions for a more flexible lookup.
2. Create an array formula to handle multiple results.

=INDEX(return_range, MATCH(lookup_value, criteria_range, 0))

This formula retrieves the first match. For multiple results, consider using it within an array formula or adapting it accordingly.

Array Formulas:

1. Use Ctrl+Shift+Enter to input array formulas.
2. Adjust the formula to handle multiple results.

{=INDEX(return_range, MATCH(lookup_value, criteria_range, 0))}

You can use this array formula to handle multiple results.