Excel Two Way Lookup with VLOOKUP & MATCH Functions - Excel Magic Trick 1567

In this video learn how to do a Two Way Lookup from a Rectangular Lookup Table using the VLOOKUP and MATCH functions. In our example we need to lookup a product name in the row and a month name in the column and return the intersecting value.
Reference Videos:
Excel Two Way Lookup with VLOOKUP & MATCH Functions - Excel Magic Trick 1567
Excel Two Way Lookup with INDEX & MATCH Functions - Excel Magic Trick 1568

[ogwalfrancis]

I love the combination of the two lookup functions, thanks Mike

[MuthukumarPL]

Thanks . Its easy my life like any thing thank you so much. Pls continue your service

[abhishek]

Hi, Good Morning
I am an Indian and I am huge fan of you. Your video is very helpful for every Accountant so thank's a lot.

[wayneedmondson]

Hi Mike.. just for fun.. exercising my brain to think about other function combinations that would perform a 2-way lookup. Came up with these against your data set:
=OFFSET(C7, MATCH(B14, B7:B12, 0)-1, MATCH(B15, C6:N6, 0)-1)
=INDEX(C7:N12, MATCH(B14, B7:B12, 0), MATCH(B15, C6:N6, 0))
={SUM(IF(B7:B12=B14, IF(C6:N6=B15, C7:N12)))} - requires CSE
=HLOOKUP(B15, B6:N12, MATCH(B14, B6:B12, 0), 0)
The fun is endless at ExcelIsFun : )) Thumbs up!!

[richardhay]

Valuable because many users do not seem to be aware that VL can be used for efficient 2-way.

[Mr_Fission]

Exactly what I needed, thank you so much! Been using excel for years and never knew about Match().

[simdog]

I use this all the time, it is a life saver, especially when the spreadsheet is owned by a 3rd party and they insist on adding and removing columns

[GeertDelmulle]

The dynamic conditional formatting is equally impressive as the combined lookup :-)

[AtanasNenov]

Nice and easy, great tip!

Btw, I've noticed that you count the column number for vlookup on this and some other videos. A small tip from me (although surely you're aware, but someone from the audience might not be) - if you start from the leftmost column of the lookup array and go right (with shift and right arrow key), there will be a tooltip near the cursor with the column count thus far, so when one reaches the desired column, they can make a mental note of it and keep extending the array (if needed). I find it really useful when number of columns is high.

[sachinrv]

Very useful video Mike I personally love this combo VLOOKUP and MATCH, as MATCH makes the lookup array dynamic. Another approach can be adding a helper row in between rows 6 & 7 with numbers 1, 2, 3, 4, 5, 6, 7 so on in B7 to N7 cells, and then column index number will be delivered by HLOOKUP to assist VLOOKUP. But MATCH does is effortlessly. These videos are certainly adding new ides to our brains. Cheers :)

[SyedMuzammilMahasanShahi]

Lovely video. Thanks for the share Mike.

[johnnyballgameFOD]

How explaining the conditional formatting to highlight the row and column in yellow and the exact cell in red. I have a decent idea how to go about it, but wonder if you used a simpler method.

[nsanch]

Thank you Mike. Hey Mike are you ever going to write another book? Slaying Excel Dragons was a blast and CTRL+Shift+Enter is a masterpiece. Just wondering if you had any plans to put something else out there in hardcopy.

[deepakmirchandani]

great and useful video sir. further can we extract values by vlookup from a cell or an array, having already a vlookup formula?

[mehyar]

Hello dear, what is the use of this formula if I can just do it with one way? Like =vlookup(B14, B6:N12, 5, 0)

[PeggywongPeggy]

how to cosolidate all worksheet into one worksheet then sum up everythings?

[emmafeng]

Hi Mike, thanks for sharing. I just have a silly question as i just watched another video of yours about two way lookup using Index &Match. so what is the main differences of this method and Vlookup&Match for this two way lookup? Thanks a lot ~~

[imranmohammad]

Hi sir,
Hmare pass employee ka data h jisme jisme unki do I'd di gayi h, ek Regd I'd or dusri Employee I'd
Kya hm vlookup formula ka use karte hue dono I'd m se search karna chahte ya fr do me se kisi ek I'd se data search kr skte

[sanjibpramanik]

This is not a example of two way lookup.