We have used H3 as our VALUE, $C$5:$C$9 as our LOOKUP Range and $E$5:$E$9 as our RESULT RANGE.

With this example we want Excel to return the price of the part number lookup. 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. 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.Įxample 2.

Note the absolute referencing around the range values, so they remain static when we copy the formula down our column of data. We have used D4 as our VALUE, G2:G13 as our LOOKUP Range and H2:H13 as our RESULT RANGE. Here are our sales figures per sales employee and a range of bonus percentages that are applicable.įollowing the syntax of the LOOKUP Function our formula looks like this.

We can use LOOKUP to easily calculate the bonus applicable to be paid to our sales force depending on their monthly sales figures. What Bonus Percentage Will I Pay My Salesforce?. So let’s put this function to the test with a couple of examples. If this parameter is omitted, it will return the first column of data. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for value in this range. Lookup_range – this is a single row or single column of data that is sorted in ascending order. Value – this is the value you want to search for in the lookup_range. Let’s take a look at the syntax to understand the arguments required in the function. 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. 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. Today let’s look at a an Excel function that can return a value from a range (a row or a column).

Hello Excellers and welcome to another #formulafriday.

How to find a range in excel