How to Return Multiple Matches in one Cell Using a lookup?

VLOOKUP is great for looking up a value and returning another corresponding value. But what happens when the first match just isn’t enough. I’ve been tasked with finding a way to put return not just the first but the second, and third match.

At first, I tried to utilize helper cells but that wasn’t good enough and they wanted me to cram the whole solution into one cell formula.

Here’s how to do it. Are you Ready?

 

IFERROR(INDEX($G$4:$G$36,SMALL(IF($A4=$F$4:$F$36,ROW($F$4:$F$36)-ROW($F$4)+1),1)),””)&IFERROR(INDEX($G$4:$G$36,SMALL(IF($A4=$F$4:$F$36,ROW($F$4:$F$36)-ROW($F$4)+1),2)),””)&IFERROR(INDEX($G$4:$G$36,SMALL(IF($A4=$F$4:$F$36,ROW($F$4:$F$36)-ROW($F$4)+1),3)),””)

I’m sorry about that monstrosity lets break it down.

IFERROR(INDEX($G$4:$G$36,SMALL(IF($A4=$F$4:$F$36,ROW($F$4:$F$36)-ROW($F$4)+1),1)),””)

OK that looks a little better but still kind of cryptic

IF($A4=$F$4:$F$36,ROW($F$4:$F$36)-ROW($F$4)+1)

First thing, we evaluated if $A4 is in the Selection range. It looks at the entire range assigning True/False to each row depending on if there’s a match

keepersecurity.com

Latest Blog Posts

Learn about SQL joins using Excel

Learn about SQL joins using Excel

Why do we join tables in SQL? Tables that logically represent information are the foundation of relational databases. The ancient rules of database normalization remind us to arrange database tables to reduce redundancy and increase flexibility. An adequately...

Super Bowl Squares Template

elink.io | See Original The Superbowl is almost here. If you are tasked with creating blocks for a pool, there's still time to pull out your ruler, draw up a grid, and wrangle a bunch of friends into writing their names in the squares. Or, spare yourself some time...

How to Delete every other Row in Excel

How to Delete every other Row in Excel

Have you ever needed to delete every other row in Excel? You are not alone.   There are a few ways to accomplish it. It's a pretty good candidate for a VBA solution, but I'm going to show you how to quickly delete every other row without using VBA or a plugin. I...

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Microsoft Excel has roughly 750 million users all over the world. It is an essential, all-purpose business tool. This spreadsheet software can fill a wide range of business needs. Every business professional needs to get familiar with Excel. Your proficiency level...

Select the Email Service Provider that is right for you.

Select the Email Service Provider that is right for you.

  Excelshortcut.com commits to continuous improvement. I've wondered if our Communication distribution process is lacking. Is there a more effective tool to help us share valuable content? Mailchimp has been ExcelShortcut.com's tool for email creation and...

Insert Multiple Rows into SQL table Using Excel as a Template

Insert Multiple Rows into SQL table Using Excel as a Template

I hope you followed the previous tutorial where we checked Created some brand new database tables using Excel as a template. Let's populate these tables with meaningful data. Okay, it might not be the most meaningful dataset since I just made this data up using...

Planning Software
Share This