MLOOKUP: try this instead of VLOOKUP

Here is what you can achieve using this tool: 

There are a handful of limitations in VLOOKUP function. Accountants tend to overuse this function and at some point you must have thought – if only this favorite Excel function could pull all the matches rather than only returning the first match. 

If you are not aware of this problem let me explain this for you. If the lookup value column contains duplicate values then VLOOKUP will only extract the first value.

In the above screenshot, you can see there is a list of sales achieved by Sales people in quarter 1 and 2. In cell F2, there is a VLOOKUP formula to extract the sales for Lee. The problem is that it only extracts the first sales figure which is in quarter 1. If I wanted to extract Lee’s sales in quarter 2 instead, VLOOKUP will not be able to do this. VLOOKUP doesn’t allow you to extract the nth value.

Here is a link to the custom excel function the arguments that it supports. This custom function solves the exact limitation of the VLOOKUP function outlined above. 

Steps to Install:
Step 1: Download the Excel Addin File from this link. Phoca Download

Step 2: Install the Addin File
(i) Open your “Microsoft Excel”
(ii) Go to “Files:
(ii) Go to “Options”
(iv) Go to “Add-ins” tab in the new window
(v) At the bottom at “Manage” Section choose “Excel Add-ins” and click “Go”
(vi) Click “Browse” and browse and select the file downloaded from Step 1
(vii) Click “OK” and return to Excel Spreadsheet

Step 3: You can start to use the tool

Step 4: The Function
MLOOKUP(lookup_value, table_array, col_index_num, [remove_duplicates])

Lookup_value: Select the cell where search values will be entered.
Table_array: The table range, including all cells in the table.
Col_index_num: The data which is being looked up. The input is the number of the column, counted from the left:
Remove_Duplicates: Optional Argument. FALSE by Default; FALSE returns all matches; TRUE returns only unique matches

Follow this post for more updates. You can later download more latest version of the forex tool here in this same post.