Look Who it Was Random video

Excel - VLOOKUP to Match Two Values - Concatenated Keys - Episode 1203

439 views · Published 24 May 2010 · 2:22 · Indexed 5 October 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: How can you do a lookup to find records that match 2 columns? Episode 1203 shows how to use a concatenated key to solve the problem.

Welcome to another episode of the MrExcel podcast, where we bring you the best Excel tips and tricks to help you become an Excel pro. In today's episode, we will be discussing a common question that was asked in two of my recent seminars in Missouri and Illinois. The question is, how do you perform a lookup for two values in Excel? This is a great question and one that many Excel users struggle with. But don't worry, I have the solution for you.

The scenario is that you have a list of GL entries with company names, account numbers, and corresponding amounts. Now, you need to find the amount for a specific company and account. This is where the lookup for two values comes in. There are two ways to do this, and today we will be looking at the easier method. Tomorrow, we will explore the harder method, but trust me, once you see it, you'll be glad we started with the easier one.

So, let's get started. The first step is to insert a new column and name it "Key". This key will be a combination of the company name and account number. You can use any method you prefer, but I will be using the concatenation method. This involves using the "&" symbol and adding the company name and account number within quotes, separated by a symbol of your choice. Once you have the key, you need to make sure it is unique. This is important for the lookup to work correctly.

Now, we can use the VLOOKUP function to perform the lookup. We will use the same formula as we would for a regular VLOOKUP, but this time, we will be looking up the concatenated key in our table. This will ensure that we get the correct amount for the specific company and account. And there you have it, the lookup for two values is complete. You can easily change the company or account number and get the corresponding amount. This method is simple and effective, but tomorrow, we will explore a different approach that may be more suitable for certain scenarios.

Thank you for tuning in to this episode of the MrExcel podcast. I hope you found this tip helpful and will use it in your future Excel projects. Don't forget to subscribe to our channel and hit the notification bell to stay updated with our latest episodes. We will be back with more Excel tips and tricks to help you excel in Excel. 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) Question about 2-way lookup
(00:34) Two ways to solve the problem
(00:44) First method: Concatenation
(01:05) Suggestion from audience
(01:15) Using VLOOKUP with concatenated key
(01:45) Conclusion and preview of tomorrow's method
(02:01) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
2-way lookup
Changing data
Concatenate columns
Data manipulation
Easier method
Formula for lookup
GL entries
Netcast from MrExcel
Unique key
VLOOKUP formula


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

More from this channel