MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can You Return All VLOOKUP Values?

October 16, 2018 - by Bill Jelen

Can You Return All VLOOKUP Values?

VLOOKUP is a powerful function. But I often get a question in one of my Power Excel seminars from someone who wants to know if VLOOKUP can return all of matching values. As you know, the VLOOKUP with False as the fourth argument will always return the first match that it finds. In the following screenshot, cell F2 returns 3623 because it is the first match found for job J1199.

VLOOKUP returns the information from the first match
VLOOKUP returns the information from the first match

The question, then, can VLOOKUP return all of the matches?

VLOOKUP will not. But other functions can.

continue reading »

More Reading

A Look Back At 13 Years of The MrExcel Podcast

A Look Back At 13 Years of The MrExcel Podcast »

October 12, 2018 - by Bill Jelen

In the summer of 2005. I was flying to Toronto every month to record two appearances on Leo Laporte’s Call for Help TV Show. On the way home, I was having dinner at the airport bar and the guy sitting next to me said a sentence that was a complete mystery to me: “Did you know that Leo’s TWiT Podcast is the Number One Podcast on iTunes?”

Find One-Hit Wonders with UNIQUE

Find One-Hit Wonders with UNIQUE »

October 4, 2018 - by Bill Jelen

UNIQUE is one of the new dynamic array functions in Excel. Using the second or third arguments, you can control if the function returns the UNIQUE or DISTINCT list of items. You can also control if UNIQUE works row-wise or column-wise.

read more articles »

IMA Data Analytics Leveraging Excel

Featured Products

Power Pivot and Power BI

Power Pivot and Power BI »

Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data.

Excel JavaScript UDFs Straight to the Point

Excel JavaScript UDFs Straight to the Point »

JavaScript custom functions - UDFs can be used like any other native functions or UDFs in Excel. This book shows the process of creating JavaScript UDFs in Excel Developer Preview for Office Insider program subscribers.

M is for (DATA) MONKEY

M is for (DATA) MONKEY »

Despite the moniker "data monkey", we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

Excel 2016 in Depth

Excel 2016 in Depth »

Experienced with Excel? Don’t let Excel 2016 make you feel like a beginner again! This new full-color edition of the bestselling book has been completely overhauled. Gone is unnecessary and rarely used content; emphasis is on the most-used and new aspects of Excel 2016.

Excel Video Medley

Excel Video Medley »

Excel Video Medley covers everything from Pivot Tables to VLOOKUP to VBA. Learn how to create scientific graphs. Use Excel for statistics.

Your Excel Survival Kit

Your Excel Survival Kit »

This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.

see more products »