I proudly developed a tiny Excel Add-In that performs lookups in a smart way and requires only two FIXED inputs, the search database and the name of the column to search from.
However the function will be able to detect the following:
- the value you are trying to retrieve other values based on (usually would be on the left of the cell containing the formula – illustrated based on the sample file)
- and the name column to retrieve the value from (usually would be on the top of the cell containing the formula – illustrated based on the sample file)
I named the function as “SLOOKUP” and it stands for Smart Lookup.
Here’s the link to the AddIn and below are the steps to activate it:
UPDATE: Copy the downloaded AddIn (with xlam extension) to
C:\Users\<Your user folder>\AppData\Roaming\Microsoft\AddIns
before you activate it. (14/08/2015)
UPDATE 2: Links to files were broken. It’s been fixed. (18/08/2015)
To activate an Excel add-in
- Click the File tab, click Options, and then click the Add-Ins category.
- In the Manage box, click Excel Add-ins, and then click Go. The Add-Ins dialog box appears.
- In the Add-Ins available box, select the check box next to the add-in that you want to activate, and then clickOK.
After activating the AddIn, you can download the sample file. (below picture is based on the sample file)
Navigate to Example 1 (Cells A13:C18) and note that I had only copied the headers (not in the original order too). I wanted to retrieve the Date of Birth (dob) and the Name (name) based on the (id). The formula in Cells (B15:C18) is exactly the same but it smartly knows what to retrieve from the database based on the left value (id) and top header (dob or name).
The syntax is =SLOOKUP(database_range, column_to_retrieve_values_based_on)
* NOTE: Make sure to use absolute references (use F4 or manually enter the $ sign before the columns and rows in both references)
Example 2 works the same but the (dob) and (id) values were retrieved based on the (name).
Under normal circumstances you want get performance hits, however, if you are performing thousands of lookups it will certainly be much slower compared to INDEX MATCH technique based on Excel’s built-in functions.
Please share your experience in the comments for further improvements. I have marked this as version 0.8 🙂
Also appreciate sharing this with your friends if you like it. You may suggest future ideas too.
All the best.