THE ONE FORMULA I WISH EXCEL HAD (+How To Build It Yourself!)
To ‘look up’ a value in a spreadsheet is something professionals do all the time. Think about looking up somebody’s name in the telephone directory: you find their name, then look across to get their phone number. In Excel language, we might say ‘find the value, then return a value from the same row, but in a different column.’
💻DOWNLOAD THE EXCEL FILE (CODE INCLUDED!)
https://tinyurl.com/yc443aye
Excel provides formulae to get this basic job done: VLOOKUP, HLOOKUP and, more recently, XLOOKUP. I find people love conquering these formulae for the first time, particularly VLOOKUP which seems to define you as an advanced Excel user: ‘Wow, you can do VLOOKUP?!’
So where does DLOOKUP fit into this? Spoiler alert: this formula doesn’t actually exist in Excel … but, I wish it did. And, I’m going to show you how to make it in just a few minutes.
🔥WATCH NEXT: Excel VBA For Beginners (1 MILLION VIEWS!)
• Excel Visual Basic (VBA) for Beginner...
🔥WATCH NEXT: Excel VBA Building Blocks
• How To Write Your First Excel VBA Mac...
With Microsoft adding new formulae all the time, many of which, in my view, add only marginal value, I’m surprised this function has been overlooked. DLOOKUP refers to a ‘double’ lookup: looking up values in not one, but TWO columns in a dataset.
To return to the telephone directory example: suppose I wanted to look up ‘Luna’. But, there are multiple Lunas in this telephone directory. Specifically, I want Luna who lives in Haminjaland. So, I need to look up ‘Luna’ in the name column, and ‘Haminjaland’ in the location column. It’s a double lookup. Why does this formula not already exist in Excel? It should!
It’s something I’ve been asked countless times in my career as an Excel content creator, lecturer and consultant. And, until now, I’ve not made a video on it. Formula-based solutions are possible, but horrendously complicated and beyond the reach of the average professional. My solution is clean and simple - but does call on VBA code to make the magic happen.
In this video, I show you how to quickly achieve DLOOKUP functionality with what’s called a ‘custom function’ in Excel VBA. Yes, VBA allows you, in effect, to create your own formulae, following whatever logic you need in your situation. If you need to look up values in two columns in Excel, you’ll love the demonstration. Make sure you download the Excel download file and work along with me.
Yes, this solution, being VBA-based, applies only to desktop Excel. If you’d like to see a cloud-based version of the DLOOKUP formula, created using Office Script in Excel 365, let me know in the comments below. If there are enough requests, I’ll happily put the video together.
In the meantime, I hope you enjoy getting to know the DLOOKUP formula and experience one or two ‘punch-the-air’ moments along the way.
📺WATCH NEXT
🔥Excel VBA For Beginners (1 MILLION VIEWS!)
• Excel Visual Basic (VBA) for Beginner...
🔥Excel VBA Building Blocks (MASTER VBA IN 6 PARTS)
• How To Write Your First Excel VBA Mac...
🔥Excel VBA To Move Data Around A File (INSTANTLY!)
• Excel VBA To Copy Data From One Sheet...
🔥Excel VBA Long Form Tutorial (COMPLETE APPLICATION BUILD)
• Learn Excel VBA From Beginner Level W...
Watch video THE ONE FORMULA I WISH EXCEL HAD (+How To Build It Yourself!) online without registration, duration hours minute second in high quality. This video was added by user Tiger Spreadsheet Solutions 26 July 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 1,832 once and liked it 47 people.