Now VLOOKUP is simply an essential skill for any serious Excel user. We organize data into rows and columns in spreadsheets, so ‘looking up’ is something we want to do all the time: locate a value on one row and return a value on the same row – but in a different column.
🔥Members' Monday FREE 1-hour taster session:
tinyurl.com/4vs8kn7k
📊Download File Link
tinyurl.com/534dzftd
Which is why VLOOKUP is so important in Excel practice. Moreover, I find it has something of a mythical quality and a kind of symbolic importance in people’s Excel learning: once people know VLOOKUP, they’re an advanced Excel user! More power to them …
I love VLOOKUP too. But, over the years, I’ve become more aware of its limitations. One such limitation – that you’ll encounter frequently in Excel modelling – we deal with in today’s video.
TIMESTAMPS
00:00 When VLOOKUP Doesn’t Work
00:15 Why I Don’t Use XLOOKUP
00:48 How To Set Up MATCH
01:28 Conceptual Explanation Of MATCH
02:04 Why OFFSET / MATCH beats INDEX / MATCH
02:54 How To Combine OFFSET and MATCH
03:47 Testing The OFFSET / MATCH Combination
04:15 About Our Members’ Monday Community
A limitation of VLOOKUP is it ‘look ups’ a value in the left-most column of the selection we specify. If that sounds very abstract, simply download the Excel file accompanying the video to see what I mean. In other words, it only works if our data is favourably laid out with our target data over on the left. That doesn’t always happen.
Microsoft has dealt with this problem recently by introducing XLOOKUP. This new(ish) formula allows you to specify any column in the dataset as the ‘lookup’ column, which means no need to re-format the data with the lookup column on the left. So, what’s the problem?
Well, XLOOKUP is only available in Excel 365 and, even three years after its introduction, plenty of people are still working on legacy versions of Excel – Excel 2019 and earlier. And you do want others to be able to use the files you build … right?
Which restricts us to long-established formulae in Excel. That’s ok for me, because combining two together in simple but powerful fashion is more than what’s needed to get the job done.
Yes, INDEX / MATCH is a possibility. INDEX allows us to navigate to any row or column number in a dataset we specify. But, that’s precisely the problem, for me, with INDEX – what if we don’t know the size of the dataset we’ll be working with? And you don’t want to deal with tables or dynamic ranges? I feel your pain!
Which is why, for me, OFFSET / MATCH is the winner here. Because OFFSET requires just an anchor point – as opposed to a whole range, table or ‘array’ – to get things moving. OFFSET moves away from this anchor point by a certain number of rows and columns. Combined with MATCH, it’s exactly what we need to do what VLOOKUP can’t.
What do you think about the solution I demonstrate in the video? Have you downloaded the Excel file and worked along with me? Do you prefer other solutions such as XLOOKUP and INDEX / MATCH? Let me know in the YouTube comments, I’ll get back to you there!
🔥ABOUT MEMBERS' MONDAY
This video is taken from our unique and exclusive Members' Monday learning community.
Looking to build your data analysis skills in a supported environment with expert access, with a group of like-minded individuals?
Need structured materials with a practical focus, and a place to go for help?
Want to take a long-term approach to your learning and get real improvement, rather than lurching from one problem to the next?
You'll love our Members' Monday community!
🔥Members' Monday FREE 1-hour taster session:
tinyurl.com/4vs8kn7k
- How To Do What VLOOKUP Can't Do With OFFSET and MATCH ( Download)
- Still Using VLOOKUP USE OFFSET and INDEX MATCH Instead | EASY Explanation ( Download)
- Stop using VLOOKUP in Excel. Switch to INDEX MATCH ( Download)
- Excel OFFSET Function for Dynamic Calculations - Explained in Simple Steps ( Download)
- INDEX MATCH Excel Tutorial ( Download)
- How to use Excel Index Match (the right way) ( Download)
- Vlookup vs Index and Match in Excel by Chris Menard ( Download)
- Excel - VLOOKUP Matching Two Lookup Values - OFFSET method - Episode 1204 ( Download)
- Excel Index Match Tutorial ( Download)
- Financial Modeling Quick Lessons: Integrating Scenarios w/ OFFSET/MATCH (Part 1) [UPDATED] ( Download)
- How to FIX SPILL Error in Excel - WHY it's NOT ALWAYS Obvious! ( Download)
- How to VLOOKUP in Excel in 1 min #excel ( Download)
- Excel INDIRECT Function: Lookup Values in Different Sheets / Excel Tabs ( Download)
- Excel Functions to Return Datapoints: INDEX, OFFSET, VLOOKUP, and MATCH ( Download)
- What's the Offset Function | Microsoft Excel ( Download)
