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



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.

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

20 thoughts on “Excel Magic Trick 781: Three Way Lookup: INDEX and MATCH and Concatenated Ranges & Cells

    HasuProx

    (September 18, 2015 - 6:07 pm)

    This is brilliant, could have saved myself hours of frustration if i'd just looked here first. XD

    Alessandro Oliveira

    (September 18, 2015 - 6:07 pm)

    Excellent, easy to understand and practice.

    william wegh

    (September 18, 2015 - 6:07 pm)

    excellent stuff!!

    Anirban De

    (September 18, 2015 - 6:07 pm)

    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 …

    noemi avilla

    (September 18, 2015 - 6:07 pm)

    =INDEX(C6:H20,MATCH(A2&B2,A6:A20&B6:B20,0),MATCH(C2,C5:H5,0))

    "&" does not work in my excel sheet.
    What shall I use instead of "&"?

    Help please.

    Yaseen Maniyar

    (September 18, 2015 - 6:07 pm)

    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

    Stephen Emmanuel

    (September 18, 2015 - 6:07 pm)

    I honestly think that you are THE BEST excel expert / teacher out there!!! … I;ve used your videos time and time again, and you're awesome.  Thanks for sharing knowledge and making our lives easier!

    frgfly

    (September 18, 2015 - 6:07 pm)

    Explain to me why I spent $600 on AICPA Excel webcast, which taught me how to insert rows (im an advanced user lol) when this gold is sitting here ?

    Toni G

    (September 18, 2015 - 6:07 pm)

    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.

    Dr.Yogesh Sharma

    (September 18, 2015 - 6:07 pm)

    Is there any way to match/ see similar values in two or more data sets/ excel sheets?

    Siddarth garla

    (September 18, 2015 - 6:07 pm)

    can you help me with a 5*5 table, i need to get the column header nd the row header when i type the value that is present in the table in a particular cell. It'll be really helpful

    Aubrey Cermak

    (September 18, 2015 - 6:07 pm)

    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?

    Andy Byma

    (September 18, 2015 - 6:07 pm)

    This is fantastic.  Thanks!

    ALFA164S

    (September 18, 2015 - 6:07 pm)

    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

    count grishnackh

    (September 18, 2015 - 6:07 pm)

    how can I use this when i am just using two sheets??

    Jennifer Pickard

    (September 18, 2015 - 6:07 pm)

    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?

    nailskorner

    (September 18, 2015 - 6:07 pm)

    I searched through all your videos, and finally found what I was looking for in this particular video. Thank you sooooo much, Mike.. You are the best!

    Aleksey Eremenko

    (September 18, 2015 - 6:07 pm)

    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.

    Vikas Jangid

    (September 18, 2015 - 6:07 pm)

    Great explanation…..but could u explain also how did you highlighted intersection?? 
    🙂 :)

    Din Mohammad Samimi

    (September 18, 2015 - 6:07 pm)

    Great job

Leave a Reply

Your email address will not be published. Required fields are marked *