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

DOWNLOAD EXCEL WORKBOOK

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:

=SUM(LOOKUP(C13:C23,

excel lookup 1st argument

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

=SUM(LOOKUP(C13:C23,E13:E18,

excel lookup 2nd argument

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

=SUM(LOOKUP(C13:C23,E13:E18,F13:F18

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:

{=SUM(LOOKUP(C13:C23,E13:E18,F13:F18))}

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

HELPFUL RESOURCE:

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Two Way Lookup Using The SUM Intersect Function There are various lookup functions that bring back values from a range of data like the VLOOKUP, INDEX, INDEX-MATCH and OFFSET functions, just to name a few.There is another lookup formula that you can use that will return the intersection of two Named Ranges!This is done...
Cleaning Data with Excel’s TRIM Formula What does it do?Removes unneeded spaces in your text, except single spaces in between wordsFormula breakdown:=TRIM(text)What it means:=TRIM(text that you want extra spaces to be removed)In the quest for cleaner data, one of the common scenarios is remo...
Match Two Lists With The MATCH Function I am sure that you have come across many occasions where you have two lists of data and want to know if a specific item in List1 exists in List2.Well I have!With the MATCH function you can verify if a cell´s item in List1 exists in List2.The function will return the r...
In-Cell Bar Charts with the REPT Function When you are creating an Excel Dashboard and are limited by space and do not want to insert a chart, you can easily create an in-cell bar chart using the RPT (repeat) function.The RPT function uses the vertical bar character | as the first argument: text and references the va...