How to Use VLOOKUP With IF Statement in Sheets

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.

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:

• 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.

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.

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:

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.

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:

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. 

If you want to learn how to automate your Google Sheetsreports with data straight from Google BigQuery, dive into this detailed article.

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:

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.

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.

• 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.

Google Sheets is loaded with an array of robust formulas designed to make data analysis more efficient:

• XLOOKUP: Elevating beyond VLOOKUP, XLOOKUP enhances the capability to search and retrieve data within your spreadsheets, offering greater flexibility.

• ARRAY: Ideal for executing calculations on a wide array of data points simultaneously, this formula outputs the results in an array.

• IMPORT Functions: Essential for importing data from various sources, such as websites, other spreadsheets, or RSS feeds, directly into your sheet.

• Pivot Table: Aids in the effective summarization and analysis of data, enabling the rapid identification of patterns and trends via automated organization.

• QUERY: Uses a language similar to SQL for performing complex data manipulations in your spreadsheet, including sophisticated filtering, sorting, and aggregating.

• CONCATENATE: Merges multiple pieces of text into a single string, making it easier to combine text from different cells.

• UNIQUE: Identifies and retains only the unique values from a given data set, effectively eliminating any duplicate entries.

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.