Excel Lookup Series #11: INDEX & MATCH functions Two 2 Way Lookup

See how to use the INDEX & MATCH functions together to lookup something in a table given a row position and column position. Yes, it's true: You can look something up at the intersecting cell given a row number and column number, and you don't even have to count the number of columns or rows!!!

See how to locate a payroll tax with a 2-way lookup (Income and Allowances)

Two 2 Way Lookup

YTL Excel #119: Amazing Lookup Solution!
Excel Lookup Series #11: INDEX & MATCH functions Two 2 Way Lookup
Excel Name Trick #7: INDIRECT function, Names and LOOKUP
Mr Excel & excelisfun Excel Trick #1: Two 2 Way Lookup
MrExcel's Learn Excel #965 - Two-Way Lookup
Excel Magic Trick #136: Two 2 way lookup with VLOOKUP & MATCH


In this Series learn 15 amazing ways to look things up in Excel. We will look at the functions VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, CHOOSE, and the non-function lookup formula using the intersector operator. We will look at simple lookups all the way to complicated, yet efficient methods to look things up in Excel.

This is a logical (beginning to end) story about most of the lookup situations you may encounter in Excel.

Category:

Science & Technology

Tags:

• Excel
• INDEX
• MATCH
• function
• Index
• intersecting
• cell
• row
• column
• Formula
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[DollRN4u2]

All of your videos are awesome and so are you. Anyone who can do this stuff is awesome to me. Now, how do I take your financial excel formulas and put them into my exercise program for my ole' seniors? I get a couple of steps and then I go from blank to nuts to wanting to throw the puter outta the window? ....Seriously, do you have a private website that I can go to and get help? I just don't get it after the if, vlookup, and match columns. HELP!!! PLEASE....

[ExcelIsFun]

@DollRN4u2 , I do not do consulting.

Try this alternative site that is set up specifically to ask Excel questions and have dialog:

mrexcel [dot] com/forum

(mrexcel site is not affiliate's with excelisfun at YouTube)

This site has many people who can help you get the solutions you want.

If you ask a carefully constructed question, you will get answers quickly

[romepivot]

Great video, I love it 

[ExcelIsFun]

@romepivot , I am glad that you like it!

[Sherlock69Holmes]

Hello Mike ,

Applied the same logic , but turned dataset to table & created two lists for Column & Row Headers.

Below is my function

=INDEX(Table1[[2009]:[2012]],M­ATCH(I7,MonthNameList,0),MATCH­(I6,YearNameList,0))

---when selected Apr in i7 - MATCH(I7,MonthNameList,0) returns 4 But MATCH(I6,YearNameList,0) -returns #N/A

Can you please tell where did it went wrong ?

Note : Creted table , so lists will increase dynamically .

[ExcelIsFun]

@Sherlock69Holmes , I do not know. Post this:

Below is my function

=INDEX(Table1[[2009]:[2012]],M­­ATCH(I7,MonthNameList,0),MATC­H­(I6,YearNameList,0))

---when selected Apr in i7 - MATCH(I7,MonthNameList,0) returns 4 But MATCH(I6,YearNameList,0) -returns #N/A

Can you please tell where did it went wrong ?

to:

mrexcel [dot] com/forum

[BeastMaj0r]

Sir - you are an absolute legend!

[ExcelIsFun]

Just a guy having fun with Excel! I am glad that the videos help!

[DollRN4u2]

Thanks for your suggestion.

[cgeorge2k9]

excellent set of tutorials. crazy tutor

[Sherlock69Holmes]

@ExcelIsFun

Here MonthNameList - Jan - Dec  YearNameList - 2009 -2012

i7 is where i select Month & i6 is where i select Year .

[LYLxd]

You make it so fun. Appreciate it, it's so fresh :)