{"id":2391,"date":"2016-02-24T23:14:40","date_gmt":"2016-02-24T22:14:40","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2391"},"modified":"2023-11-30T21:45:23","modified_gmt":"2023-11-30T20:45:23","slug":"total-bonus-due-with-an-array-lookup-formula-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/total-bonus-due-with-an-array-lookup-formula-excel\/","title":{"rendered":"Total Bonus Due With An Array Lookup Formula"},"content":{"rendered":"

The LOOKUP function has three arguments, it is a like a simplified VLOOKUP function:<\/p>\n

What does it do?<\/strong><\/em><\/p>\n

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)<\/p>\n

Formula breakdown:<\/strong><\/em><\/p>\n

=LOOKUP(lookup_value<\/span>, lookup_vector<\/span>, result_vector<\/span>)<\/p>\n

What it means:<\/strong><\/em><\/p>\n

=LOOKUP(The value that you want to lookup<\/span>, Range that contains the values to be lookup up<\/span>, Range that contains the values to be returned<\/span>)<\/p>\n

** Note that the LOOKUP function uses the Approximate Match argument automatically (TRUE argument in a VLOOKUP), so therefore the result_vector<\/span> has to be sorted in ascending order **<\/em><\/p>\n

Read this blog that explains the VLOOKUP using the Approximate or TRUE argument<\/a><\/span><\/p>\n

\n

Say that you\u00a0want 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.<\/p>\n

You will need to have your data set up into\u00a0three separate columns, one for the Lookup Values<\/em><\/span>\u00a0(Sales made by the Sales Rep), another for the Lookup Range<\/em><\/span> (Sales bonus threshold) and the last one for the Results Range<\/em><\/span> (Bonus payable in ascending order)<\/p>\n

\"Total<\/a>
\n