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.

Home Microsoft Office Excel Excel Magic Trick 781: Three Way Lookup: INDEX and MATCH and Concatenated...

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

Excellent, easy to understand and practice.

excellent stuff!!

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 …

=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.

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

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!

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 ?

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.

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

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

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?

This is fantastic. Thanks!

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

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

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?

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!

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.

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

🙂 :)

Great job