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:
- Choose cell G3.
- 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â)))
- 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
- 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.
- 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.â
- 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:
- Select cell G3.
- 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â)
- Â 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:
- Select cell G10.
- 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â)))
- 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:
- Combine the INDEX and MATCH functions for a more flexible lookup.
- 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:
- Use Ctrl+Shift+Enter to input array formulas.
- 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.