Hello Excellers and welcome to another #formulafriday. Today let’s look at a an Excel function that can return a value from a range (a row or a column). LOOKUP is one of Excel’s lookup and reference function as will search for the value in the lookup range and return the result range that is in the same position in a second row or column. What is really handy with LOOKUP is that if it cannot find an exact match then it will return the largest value in the lookup range that is less than or equal to the lookup value. Let’s take a look at the syntax to understand the arguments required in the function.
LOOKUP Syntax
LOOKUP(value, lookup_range, [result_range])
where
value – this is the value you want to search for in the lookup_range.
lookup_range – this is a single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range.
result_range – the value is optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, it will return the first column of data.
So let’s put this function to the test with a couple of examples.
Example 1. What Bonus Percentage Will I Pay My Salesforce?.
We can use LOOKUP to easily calculate the bonus applicable to be paid to our sales force depending on their monthly sales figures. Here are our sales figures per sales employee and a range of bonus percentages that are applicable.
Following the syntax of the LOOKUP Function our formula looks like this. We have used D4 as our VALUE, G2:G13 as our LOOKUP Range and H2:H13 as our RESULT RANGE. Note the absolute referencing around the range values, so they remain static when we copy the formula down our column of data. Excel has easily returned the correct allocation of bonus percentage as the largest value in the lookup range that is less than or equal to the lookup value.
Example 2. What Is The Price Of That Bike Part?
We can also use the LOOKUP function to find the price of a parts, when we know the part number for them, but not the name or price. Take a look at my sample data set below.
With this example we want Excel to return the price of the part number lookup. Our formula again is straightforward. We have used H3 as our VALUE, $C$5:$C$9 as our LOOKUP Range and $E$5:$E$9 as our RESULT RANGE.
We can now lookup any Part Number in cell H3 and we can retrieve the price of it from the inventory data.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
