In this tutorial we are going to use the LOOKUP function to find the last match in an array and return the corresponding value from another column from that row.
Thanks to Mr. Excel, Bill Jelen, for this tip found in his book “Power Excel”! This book has hundreds of great solutions using Excel to common and unusual problems.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a list of salespeople and their sales. I want to be able to pull from this list the last sale for a given employee:
I’ve set up my data validation drop down list in cell E2 and I want to insert a formula in F2 that will pull the last sale for that employee:
I’ve also set up Conditional Formatting so that when I select a salesperson, their sales will be highlighted in the list just so I can check my formula:
The formula I will enter into cell F2 is:
The IFERROR function I’ve wrapped my formula in is just so that if I don’t choose a salesperson from the list, I won’t get an error.
Here is how the formula works. I’m using a LOOKUP function with the following syntax:
Our LOOKUP value is the number 2, our lookup_vector is 1/($B$2:$B$18=$E$2), and our result vector is the sales in column C.
The key here is the lookup_vector 1/($B$2:$B$18=$E$2). When I run the comparison of $B$2:$B$18=$E$2, I get the following results:
Note the three “TRUE” results. This is where the list of salespeople match the selection in cell E2 of “Sue”. Remember, in Excel, a TRUE is a “1” and a FALSE is a “0”. So when I divide “1” by that string of ones and zeros, I get:
So, with the LOOKUP function we are looking for the number “2” in a series of #DIV/0! and number ones. The LOOKUP function acts like a VLOOKUP with an approximate match. It goes down through the list and looks for that number, if it doesn’t find it, it will go back to the last number that was less than the number it is looking up. In this case, the last number “1” in the list!
Here is an example. In this case, I am looking for the number 12 in this array. Since it can’t find it, it will return the value next to the last number “9” and give us an “H”:
So, when I make a selection from my dropdown list, it will return the last value for that salesperson:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!