Look Who it Was Random video

Excel - Mastering Lookup by State and County in Excel: New Method Revealed! - Episode 890

2,522 views · Published 5 January 2009 · 4:14 · Indexed 29 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Mastering Lookup by State and County in Excel: New Method Revealed!

Welcome back to the MrExcel netcast! In this video, we will continue our discussion on how to do a lookup by both State and County. In the previous video, we used a concatenated key, but today we will try a different method.

First, we need to answer a couple of questions. Where does the State start? To find this, we will use the =MATCH function. We will search for the State in column A and use either ,0 or ,False at the end, just like we would with a VLOOKUP. This will tell us the row where the State starts.

Next, we need to know how many rows the State has. To find this, we will use the =COUNTIF function and search for the State in column A. This will give us the number of rows for that State.

Now, we can use the VLOOKUP function to find the County name. But instead of hard coding the table array, we will use the OFFSET function. This amazing function allows us to dynamically change the table array based on the State we choose. We will use the number of rows and columns we found earlier to determine the table array.

Every time we choose a new State, the OFFSET function will change accordingly, allowing us to easily find the County name. We can also use this method to look up other values, not just County names.

To make things easier, we can use a trick to combine all the formulas into one monster formula. This may look complicated, but it is actually just several smaller formulas combined together. This method is useful when we don't have control over the data and cannot add an extra column for the concatenated key.

Thank you for watching this netcast from MrExcel. Don't forget to like and subscribe for more Excel 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've probably seen the AC/DC video running in an Excel spreadsheet. The author did some cool tricks to deliver the WAV file to your hard drive. In Episode 891, I'll take a look at the code to extract an object from a workbook.

Table of Contents:
(00:00) Introduction and overview of topic
(00:27) Determining starting point and number of rows for chosen state
(00:38) Using MATCH and COUNTIF functions
(01:18) Implementing VLOOKUP with OFFSET function
(02:02) Demonstration with different states and counties
(03:20) Combining formula pieces for a final solution
(03:52) 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:
Building a monster formula
Butlerrecorder.oh.gov
Changing OFFSET with each State selection
Concatenated key
COUNTIF function
Lookup at State and County
MATCH function
Number of counties in Ohio
OFFSET function
Ohio records start
VLOOKUP function


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

More from this channel