Look Who it Was Random video

Excel - Mastering VLOOKUP: Using Concatenated Key for Lookup - Episode 889

789 views · Published 5 January 2009 · 2:42 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Mastering VLOOKUP: Using Concatenated Key for Lookup.

Welcome back to another episode of the MrExcel netcast! In this episode, we're going all the way back to Episode 383 where I showed you how to set up dependent validation. This is a useful trick where you can have a list of options change based on a selection made in another cell. For example, if someone chooses Ohio as their state, they will see a list of Ohio counties, but if they choose Texas, they will see a list of Texas counties instead. 

But what if you need to do a lookup for both the state and the county? Well, in today's episode, I'll show you one method that involves using a concatenated key. This means we will join together the state and county values to create a unique identifier. This is important because there may be multiple counties with the same name across different states. 

To do this, we will insert a new column and use a formula to combine the state and county values. Then, we can use this concatenated key to do a VLOOKUP and retrieve the desired value. This method does require some changes to your data, but it can be a useful solution for certain situations. 

In tomorrow's episode, we will take a look at another method using the OFFSET function. So make sure to stop back for that! Thank you for tuning in and don't forget to subscribe to the MrExcel netcast for more helpful tips and tricks. 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/



You need to do a VLOOKUP that will look up two values from a table. In Episode 889, I will show a method using a concatenated key field to enable VLOOKUP to work.

This video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents:
(00:00) Introduction
(00:10) Setting up dependent validation (Episode 383)
(00:23) Using dependent validation for different states
(00:33) Using dependent validation for different counties
(00:44) Using a Lookup to find both state and county
(00:54) Method 1: Using a concatenated key
(01:12) Creating a unique value for the key
(01:23) Using VLOOKUP to find the value
(01:50) Changing the state and county
(02:06) Method 2: Using the OFFSET function
(02:17) 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:
How to make values unique in lookup
Lookup method for state and county
MrExcel netcast - Episode 383
Ohio counties list for dependent validation
Setting up dependent validation in Excel
Solving the state and county lookup issue
Texas counties list for dependent validation
Using column C as first column in table for VLOOKUP
Using concatenated key for lookup
Using OFFSET function for lookup
VLOOKUP formula for concatenated key lookup
YouTube video on dependent validation setup


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

More from this channel