Excel Magic Trick 781: Three Way Lookup: INDEX and MATCH and Concatenated Ranges & Cells

20
337



Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm
1. See how to do a three way lookup when there are two row header variables (criteria) and one column header (criteria) variable using INDEX and MATCH functions for a normal two way lookup, but with the two row headers joined to create a single row header criteria. In essence, three way lookup is turned into a standard two way lookup. Also see IFERROR function.

20 COMMENTS

  1. Table1
    name date v1
    a 20-03-15 310
    b 20-03-15 312
    c 20-03-15 312
    a 21-03-15 310
    b 21-03-15 310
    c 21-03-15 312
    a 22-03-15 312
    b 22-03-15 310
    c 22-03-15 312

    Table2
    name date v2
    a 21-03-15 995
    b 22-03-15 991
    c 21-03-15 987
    a 20-03-15 932
    b 21-03-15 995
    c 20-03-15 982
    a 22-03-15 978
    b 20-03-15 999
    c 22-03-15 906

    I have above two tables,  I want to lookup v2 values in table2 corresponding to name and date columns in table1 and add another column beside v1 in table1.
    I tried with Index and Match function , but match function not working with date datatype. Please suggest …

  2. I have a question on vlookup, hope you will answer. 
    I have data in Column A and B, my reference is column A and I need column B data – Vlookup will solve my problem but for single reference in column A there are multiple data in B, could you please tell me which of your trick is covered this topic

  3. I want this to work over two different workbooks, I need to find a value of the same items listed in last months workbook to this months workbook as to what was a written off and return the value so I can see if there is a trend on what is being written off. Column A 'Project No.', Column B ' Project Name, Column C 'Subproject Name' Column L 'Current Month Write Off', Column Q 'Previous Month Write Off'. Each month we generate a new workbook and I need to automatically find last months write off for any listed Subproject. I've had a go at using the Index_Match however a #NA value appears.

  4. Can you do the same function if you have two rows and one column, instead of two columns and one row like in the example above?  I can get the formula to work using the data as you have it above however if I try to merely replace my ranges in the working formula, it no longer works. Any ideas?

  5. Hi I need your assistance with the 2 way index and match lookup for data I download from the web.  is there an email you can provide so I can email you my worksheet errors.  I followed your index match video but i have some problems.
    Thanks

  6. I'm been searching for this exact tutorial…THANK YOU! I've got the formula working now, but instead of populating one field above the grid I'm populating a column in a different sheet. I need to be able to copy the formula down the column and pull in the adjusted value. Is this possible?

  7. Great video. As expected, this gets the correct value and you can use that value for further calculation (for example, month over month values). Also works and across tables (column names and same-row column values), and can feed a pivot table.

LEAVE A REPLY

Please enter your comment!
Please enter your name here