Look Who it Was Random video

Excel - VLOOKUP Matching Two Lookup Values - OFFSET method - Episode 1204

480 views · Published 24 May 2010 · 4:07 · Indexed 21 September 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: VLOOKUP that matches two columns, but without using a concatenated key. This solution uses the OFFSET function to dynamically change the position of the lookup table.

Welcome back to the MrExcel netcast! In this episode, we will be continuing our discussion on how to do a lookup that requires two columns. In yesterday's episode, we used concatenation to create a key, but what if you're not allowed to add anything to the data? Well, in that case, we will have to geek out a little bit and use the MATCH function to figure out where the company we are looking for starts in the range.

To do this, we will use the MATCH function with the company name in cell E2 as the lookup value. We will also use the COUNTIF function to determine how many rows in the range have the same company name. With this information, we can then use the OFFSET function to positionally refer to the lookup table. This allows us to create a VLOOKUP formula without having to add a concatenated key.

But wait, there's an even easier way to do this! Our sponsor, MrExcel.com, makes it incredibly simple to do a 2-way lookup with just a few clicks. All you have to do is move your data to two different sheets, select the columns you want to match on, and MrExcel.com will do the rest. No need to use complicated formulas or concatenate keys. Plus, you can try it out for free with their 30-day trial. So why not give it a shot and see how much time and effort you can save with MrExcel.com?

Thank you for tuning in to this episode of the MrExcel netcast. I hope you found this tutorial helpful and that it will make your future lookups a lot easier. Don't forget to check out MrExcel.com for all your data analysis needs. And be sure to join us for our next netcast where we promise to cover something much easier. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Using MATCH function to find starting position
(00:31) Using COUNTIF to determine number of rows
(01:09) Using OFFSET function to positionally refer to lookup table
(01:48) Building VLOOKUP formula without concatenated key
(02:44) Combining formula into one big formula
(03:00) Conclusion and Sponsorship reminder
(03:18) MrExcel.com sponsorship and demonstration
(03:29) Clicking Like really helps the algorithm

This video answers these common search terms:
CONCATENATE function
Concatenated key
COUNTIF function
Dynamic VLOOKUP range
Lookup with two columns
MATCH function
MrExcel podcast
OFFSET function
Two-way lookup
VLOOKUP function

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152814/

More from this channel