How to Use VLOOKUP With IF Statement in Sheets
Masha Efy, Creative Writer @ OWOX
Vadym Kramarenko, Senior Growth Marketing Manager @ OWOX
When you're managing a store, knowing what's running low is key for smooth operations. But the usual VLOOKUP has its drawbacks – it struggles with finding the best match or dealing with several conditions at once. In our guide, we'll show you how to overcome this by using VLOOKUP with IF statements in Google Sheets. We'll explain different ways to use them and show how this combo can be a game-changer for managing your business.
Why Combine the VLOOKUP with the IF Statement in Google Sheets
The VLOOKUP function in Google Sheets is a great way to search values within a table, while the IF statement returns specific values based on particular conditions we set. When we combine them, VLOOKUP gets extra features for more versatile operations.
Let's imagine a situation where we have product items listed in 2 different restaurants. Using VLOOKUP alone, it's easy to find the price of a specific item in one database by providing details, such as the item's name, ID, the time of delivery, etc.
However, what if we needed to easily compare specific food prices between both restaurants? Or if we wanted to verify if food is prepared based on the order time? These are tasks that are challenging to accomplish using the VLOOKUP formula alone because it doesn't naturally handle such complex comparisons or checks.
Here's the syntax of a simple IF with VLOOKUP:
=IF(VLOOKUP(search_key, range, index, [is_sorted]) = logical_expression, value_if_true, value_if_false)
- search_key: The value you want to search for within a table.
- range: The range where you want to look for the value.
- index: The column number in the table from which to return a value.
- is_sorted: [optional] TRUE for an approximate match, FALSE for an exact match.
- logical_expression: An expression or reference to a cell containing an expression that represents some logical value, i.e. TRUE or FALSE.
- value_if_true: What to display if the logical_expression is met.
- value_if_false: What to display if the logical_expression is not met.
Conditional VLOOKUP in Google Sheets (How to Use with Examples)
In our examples, we'll show you a different way to use VLOOKUP by adding the IF statement in Google Sheets. This mix will help us find particular values and determine if they meet certain criteria we'll define.
IF VLOOKUP Combination for Comparison Operators
Comparison operators – greater than (>) and less than (<) – allow us to compare different values and provide a TRUE or FALSE outcome. They are particularly handy in conditional statements.
In our spreadsheet, we have a list of different foods and when they were ordered. We want to know if a Pizza was ordered or not.
Let's say, if an order was placed before 10 am, it's ready for pickup, but if it was after that time, it's not ready.
Use the following formula:
=IF(VLOOKUP(G3, B4:D12, 3, FALSE) < TIME(10, 0, 0), "Food is ready", "Food is not ready")
This way we're checking the order time using VLOOKUP. If the time is before 10 AM, it'll show "Food is ready". Otherwise, it'll say "Food is not ready.
The picture shows that the Pizza is ready for pickup, thanks to using VLOOKUP with the IF condition in Google Sheets.
IF VLOOKUP Combination to Switch Lookup Tables
Now, let's see how to use VLOOKUP with the IF statement in Google Sheets to search in 2 tables.
To find the price of a Hot Dog in a specific restaurant using both tables, we'll use this formula:
=VLOOKUP(J5, IF(J4 = "Royal Place", B5:C12, E5:F12), 2, 0)
Here's a breakdown:
- Enter the cell name of your VLOOKUP parameter, in our case, J5 corresponds to Hot Dogs.
- Use the IF statement and add information that says where to look: J4 = Royal Palace, and B5:C12,E5:F12 are our tables.
- As the prices are in the second column, index it as 2.
- Use 0 for an exact match.
- The conditions are as follows: If "Royal Place" is the chosen restaurant, the formula returns its Hot Dogs price. If the selected restaurant in the formula isn't "Royal Place", it will automatically display the data related to the second restaurant in the specified range.
Mastering VLOOKUP and IF statement is a great analytical skill. But when it comes to integrating large datasets, especially from data warehouses like Google BigQuery, there's a more dynamic approach.
Dive deeper with this read
Automate Reports in Google Sheets Using Data from Google BigQuery
IF VLOOKUP Combination for Error Handling
When we try to find data that doesn't exist in the table, like a specific food item, Google Sheets shows an error message known as #N/A (not available).
For example, if we try to find "Roasted Chicken" that is not on the menu in any of the restaurants we want to display a specific message instead of an error.
To avoid displaying this error, we can customize the response using the IF and ISNA statements combined with VLOOKUP. Here’s an example formula:
=IF(ISNA(VLOOKUP(I6, E7:F14, 2, 0))=TRUE, "Will be available by 12 PM today", VLOOKUP(I6, E7:F14, 2, 0))
The purpose of this formula is to replace the typical #N/A error with a more user-friendly message. In this case, when a specific food isn't available in any of the restaurants, the formula displays the message "Will be available by 12 PM today" instead of the error code.
The screenshot above shows that Roasted Chicken isn’t available at the moment and we can see a message that says "Will be available by 12 PM today". We achieved that using IF and ISNA statements combined with VLOOKUP.
Automate your digital marketing reporting
Manage and analyze all your data in one place! Access fresh & reliable data with OWOX BI — an all-in-one reporting and analytics tool
IF VLOOKUP Combination Control Index Column
When you use the IF statement with VLOOKUP to control the index column, it's about choosing the right column for data lookup based on certain conditions.
Imagine you have data arranged in different columns, like prices at different restaurants in separate columns (Food Fort, Royal Place, etc.). Using VLOOKUP alone might always look up data from one specific column.
By combining IF with VLOOKUP, you can change which column to look up data from, depending on what you need. This means you can dynamically choose the right column based on a condition.
For example, if you want to check prices for Food Fort, the formula will look in the Food Fort column. It makes your lookup more adaptable to different situations or conditions.
The formula uses the VLOOKUP function in combination with the IFS statement to control the index column based on the value in cell G4.
=VLOOKUP(G3, B2:D10, IFS(G4 = "Royal Place", 2, G4 = "Food Fort", 3), 0)
- G3 contains the lookup value.
- B2:D10 is the range where the data is stored.
- The IFS statement evaluates the condition in G3 to determine the column index.
- If G4 equals Royal Place the formula uses the 2nd column.
- If G4 equals Food Fort it uses the 3rd column, and so on.
- 0 ensures an exact match.
Note that there are also alternative methods like using MATCH or HLOOKUP functions that may provide more efficient solutions depending on the context.
If you work with extensive volumes of data and need a centralized platform for smooth work, OWOX BI can be beneficial.
Master VLOOKUP & IF Logic in Google Sheets!
Hone your skills with our interactive VLOOKUP and IF statement template. It's all set for you to download and start applying what you've learned. Get hands-on practice with real examples straight from this article.
Build Reports in No Time with Google Sheets
With OWOX BI, you can manage large amounts of data by importing it directly into Google Sheets and then working with it. No need for CSV files. You can then build reports, automate report updates, and share this data with every employee in your company using standard access management for Google documents.
Additionally, in the settings of the OWOX BI BigQuery Reports Extension, you can schedule automatic updates for your reports.
Save time on creating reports!
Let OWOX BI handle data processing, automatically importing results into Google Sheets.
Can this combination be used for more advanced data analysis in Google Sheets?Yes, it enables conditional data retrieval and analysis in Google Sheets.
Are there any limitations or potential issues when combining 'IF' and 'VLOOKUP' in Google Sheets?Complex formulas might become harder to manage.
What is the syntax for using 'IF' with 'VLOOKUP' in Google Sheets?Use IF to control the output of VLOOKUP by embedding it within the logical test.
Can you provide an example of when I might need to use 'IF' with 'VLOOKUP' in Google Sheets?Checking if a product is in stock and displaying "In Stock" or "Out of Stock" accordingly.
What is the main purpose of combining the 'IF' statement with 'VLOOKUP' in Google Sheets?It helps control the results of the VLOOKUP based on specific criteria.
How do I combine IF and VLOOKUP together?Put the VLOOKUP formula inside the logical test of the IF statement.
What is the difference between VLOOKUP and IF?VLOOKUP searches for data in a table, while IF gives different outcomes based on conditions.
How to use a VLOOKUP with an IF statement in Google Sheets?Combine VLOOKUP and IF statement to search for specific data and return results based on certain conditions.