How To Write An Excel VLOOKUP Formula With An IF Statement - Formula Friday

Hello Excellers, I am delighted to share another Excel #formulafriday Excel tip with you today. If you have used Excel for some time, you probably know how to use a VLOOKUP formula. If you do not then here is a really quick recap of it before we supercharge it and combine it with the IF function to give it superpowers by adding logic.

VLOOKUP RECAP SYNTAX.

=VLOOKUP(value, table, index_number, [approximate_match] )

Where

value – this is the value to search for in the first column of the table

table – this is your data set (usually at least 2 columns of data)

index_number – this is the column number in the data table from which the matching value is to be returned. Note the first column in the Table is 1.approximate_match – this is optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this argument is omitted, TRUE is the default.

An Example To Work Through.

So a quick example. I have a dataset which contains all of my product information. This includes product ID, product name, product price etc. I also have a customer order worksheet which is completed when a customer orders a product. By using the unique product ID in the customer order sheet I can pull in all of the associated product details from the product information data.

You can see that I have looked up the product ID in D3 in my product table and returned the corresponding value in column 2. This gives me the product name. Use this method to return any of the associated product details in the lookup table.

Turning Up The Volume On VLOOKUP.

Ok. Let’s turn up the volume on this and include an IF statement. This scenario I am sharing was a query by a colleague. As in my first example of a VLOOKUP, they had the same set up to bring in customer pricing to their worksheet using the product ID. This was working well until an extra variable or scenario came into the mix. They now had two price lists, one for the first half of the year, and another for the second half of the year due to pricing changes.

Their query was, how to look up both of the lists, to get the correct pricing for the correct time of year. This is exactly the type of problem that can be solved by combining VLOOKUP with IF.

This is the data before the new pricing, where they used the product ID to bring in the pricing details.

However, the second half of the year brings price changes. We need tell Excel to lookup the product ID for the first half of the year in price list 1 and the second half of the year use price list 2.

The Solution!!.

So, the solution. First, add in a helper column to our Customer Pricing, which indicates the FIRST or SECOND half of the year. We can use this as the logic to determine which customer price list to use in our VLOOKUP.

Secondly, we can start with an IF statement with the following logic.

If YEAR HALF is First, then VLOOKUP product ID in customer list 1, if YEAR HALF is NOT First then VLOOKUP product ID in customer list 1.

Ao that is fairly straightforward right? would you agree?. We can now write the formula in the Excel sheet to test our logic. Easy as that. We can now see that the correct data is pulled to our data table.