How to Add Vlookup and If Function Together in Google Sheet?

Google Sheets is an incredibly powerful tool for managing data.

One of its most useful features is the ability to use functions to automate tasks and calculations.

Two such functions are VLOOKUP and IF. When used together, these functions can help you create more dynamic and responsive spreadsheets.

In this blog post, we’ll explore how to combine VLOOKUP and IF functions in Google Sheets with a real-life example of checking product availability for order fulfillment.

What is VLOOKUP?

VLOOKUP stands for “Vertical Lookup.” It is a search function that looks for a value in the first column of a range and returns a value in the same row from another column. Here’s the syntax:

=VLOOKUP(search_key, range, index, [is_sorted])

• search_key: The value you want to search for.
• range: The range of cells to search within.
• index: The column number in the range from which to retrieve the value.
• is_sorted: Optional. If TRUE (or omitted), the first column of the range must be sorted in ascending order. If FALSE, the range does not need to be sorted.

What is IF?

The IF function allows you to perform a logical test and return one value for a TRUE result and another for a FALSE result. Here’s the syntax:

=IF(logical_expression, value_if_true, value_if_false)

• logical_expression: An expression or value that can be evaluated as TRUE or FALSE.
• value_if_true: The value to return if the logical expression is TRUE.
• value_if_false: The value to return if the logical expression is FALSE.

Combining VLOOKUP and IF

Combining VLOOKUP with IF can add a layer of logic to your data processing.

For instance, you might want to look up a value and then perform some action based on that value.

Example Scenario: Checking Product Availability for Order Fulfillment

Imagine you are managing an inventory for an online store.

You have a list of products along with their current stock levels, and you need to determine if you can fulfill an order based on the quantity requested by a customer.

Suppose you have the following inventory data:

Inventory Data:

And an order request data:

Order Request Data:

You need to determine if you can fulfill each order based on the available stock quantity.

Step-by-Step Guide

1. Set Up Your Data

• Start by entering your inventory data and order request data in separate tables in Google Sheets.
• Inventory Data (Cells A1):

Order Request Data (Cells F1):

2. Use VLOOKUP to Find Stock Quantity

Add a column for the stock quantity in your order request table. Use VLOOKUP to find the current stock level for each product.

Formula in Cell I2:

=VLOOKUP(G2, $A$2:$D$4, 3, FALSE)

How It Works:

• G2 is the Product ID from the order request table.
• $A$2:$D$4 is the range of the inventory table.
• 3 specifies that we want to retrieve data from the third column of the range (Stock Quantity).
• FALSE ensures an exact match.

Example:

• For Order ID 1001, Product ID 101 has a stock quantity of 20.

Now, drag the formula in cell I2 down to I4 to apply it to all rows.

Order Request Table After Dragging the Formula:

3. Use IF to Check Availability

Add a column for availability. Use the IF function to check if the requested quantity can be fulfilled.

Formula in Cell J2:

=IF(H2 <= I2, "In Stock", "Out of Stock")

How It Works:

• H2 is the Requested Quantity.
• I2 is the Stock Quantity retrieved using VLOOKUP.
• The formula checks if the requested quantity is less than or equal to the stock quantity. If true, it returns “In Stock”; otherwise, it returns “Out of Stock.”

Order Request Table After Applying the Formula in Cell J2:

Example:

• For Order ID 1001, the requested quantity (15) is less than the stock quantity (20), so the availability is “In Stock”.

Now, drag the formula in cell J2 down to J4 to apply it to all rows.

Order Request Table After Dragging the Formula:

FAQs

Q1: Can I use VLOOKUP with multiple conditions?

A1: VLOOKUP alone cannot handle multiple conditions directly. However, you can use other functions like INDEX and MATCH for more complex lookups.

Q2: What happens if VLOOKUP doesn’t find a match?

A2: If VLOOKUP does not find a match, it will return an error (#N/A). You can handle this using IFERROR or IFNA functions.

=IFERROR(VLOOKUP(1, A2:D4, 4, FALSE), "Not found")

Q3: Can I use VLOOKUP with text data?

A3: Yes, VLOOKUP works with text data as well as numbers. Make sure your search_key matches the data type in the lookup column.

Q4: What’s the difference between TRUE and FALSE for the is_sorted parameter?

A4: If is_sorted is TRUE or omitted, VLOOKUP assumes the first column of your range is sorted in ascending order and performs a binary search. If it’s FALSE, VLOOKUP performs a linear search regardless of order.

Q5: Can I nest more than one IF statement?

A5: Yes, you can nest multiple IF statements for more complex logic. However, it can make your formula harder to read and maintain.

=IF(condition1, result1, IF(condition2, result2, result3))

Conclusion

By combining VLOOKUP and IF functions in Google Sheets, you can efficiently determine product availability based on stock levels and requested quantities.

This method helps in managing inventory and ensuring that you can fulfill customer orders accurately and efficiently.

Whether you’re checking stock levels or performing more complex lookups, mastering these functions will enhance your spreadsheet skills significantly.

Happy spreadsheeting!