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.
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.
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!!.
Secondly, we can start with an IF statement with the following logic.
Here Is The Finished Formula.
That’s it, folks. I hope you enjoyed this instalment of Macro Monday.