The LOOKUP function has three arguments, it is a like a simplified VLOOKUP function:

What does it do?

It looks up a value (lookup_value) in one range (lookup_vector) and returns a value from the same position in a second range (result_vector)

Formula breakdown:

=LOOKUP(lookup_value, lookup_vector, result_vector)

What it means:

=LOOKUP(The value that you want to lookup, Range that contains the values to be lookup up, Range that contains the values to be returned)

** Note that the LOOKUP function uses the Approximate Match argument automatically (TRUE argument in a VLOOKUP), so therefore the result_vector has to be sorted in ascending order **

Read this blog that explains the VLOOKUP using the Approximate or TRUE argument

Say that you want to calculate the Total Bonuses due to the Sales Reps, you can do that using the SUM(LOOKUP()) formula and then pressing CTRL+SHIFT+ENTER to turn it into an Array formula.

You will need to have your data set up into three separate columns, one for the Lookup Values (Sales made by the Sales Rep), another for the Lookup Range (Sales bonus threshold) and the last one for the Results Range (Bonus payable in ascending order)

excel lookup table


STEP 1: Enter the SUM function =SUM(

STEP 2: Nest the LOOKUP function =SUM(LOOKUP(

STEP 3: Select the Lookup Values in the Sales Rep Sales column:


excel lookup 1st argument

STEP 4: Select the Lookup Range in the Sale bonus threshold column:


excel lookup 2nd argument

STEP 5: Select the Results Range in the Bonus payable column:


excel lookup 3rd argument

STEP 6: Instead of pressing ENTER to evaluate the formula, you need to press CTRL+SHIFT+ENTER to turn the formula into an Array Formula which will look like this:


excel lookup result

STEP 7: By pressing F9 on the selected formula array will give you the resulting array of numbers that get summed up (press CTRL+Z to get out of this mode when you are done checking the formula results):

excel f9 audit

Array Sales Bonuses


This post was inspired by a post from Mr.Excel´s Power Excel book

Power Excel


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

SUMIFS Function: Introduction The SUMIFS function allows you to Sum multiple criteria. For example, you can select one Sales Rep from a list of Sales Reps and select one Region from a list of Regions and return the Sum of those arguments from a Sales list.  See how easy it is... DOWNLOAD WORKBOOK ...
Vlookup Approximate Match in Excel What does it do? Searches for an approximate value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) Wha...
Advanced SUMPRODUCT Function: Conditional Date If you want to find out the total sales for a particular month, then the SUMPRODUCT function is your answer.  You can create a criteria for a specific date range, a particular month or a year. In the example below I show you how to use the SUMPRODUCT function to sum up the tot...
Return the Last Value in a Column with the Offset ... What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number of ro...