Excel - How to vlookup to return multiple values?

Overflew Published at Dev

overflew

I'm looking to use Excel to look up and return multiple reference values for a given key. VLookup does something very similar to what I need - but only returns a single match.

I assume it'll involve array-returning and handling methods, though I haven't dealt with these before. Some Googling starts to lean on the if([lookuparray]=[value],row[lookuparray]) as part of a solution - though I can't get it to return a single match...

For example, if I have this reference data:

Adam    Red
Adam    Green
Adam    Blue
Bob     Red
Bob     Yellow
Bob     Green
Carl    Red

I'm trying to get the multiple return values on the right. (Comma separated, if possible)

Red      Adam, Bob, Carl
Green    Adam, Bob
Blue     Adam
Yellow   Bob

(I already have the key value on the left - no need to pull out those values)

Any help as to how to approach handling multiple values in th this context is apprecited. Thanks.

Dan

Assuming you want a formula approach as stated (not using VLOOKUP, but still a formula), here is how I laid out the data:

Data Layout

I then used the following formula in cell C12:

=INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""), 1))

This is an array formula so after you copy and paste it into the cell you have to hit Ctrl+Shift+Enter. I then just dragged it to the right and to the bottom.

If there is no value(s) remaining it gives the #NUM! error, I gave an example for yellow in the uploaded image example.

I think a VBA/Macro approach would be a better solution if you have tons of rows.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-03-6

Comments

0 comments

How to vlookup to return multiple values using a "contains type" match, where the search string can appear anywhere in the lookup value

TOP Ranking

Article

Excel - How to vlookup to return multiple values?

Excel - How to vlookup to return multiple values?

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Inner Loop design for webscrapping

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Removed zsh, but forgot to change shell back to bash, and now Ubuntu crashes (wsl)

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

How to run blender on webserver?

Resetting Value of <input type="time"> in Firefox

Converting a class method to a property with a backing field

Ambiguous use of 'init' with CFStringTransform and Swift 3

Execute ./script.sh with a crontab

How to set tab order for array of cluster,where cluster elements have different data types in LabVIEW?

How to pass data to the ng2-bs3-modal?

Retrieve Element Tag Value XML Using Bash

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

SQL Server : need add a dot before two last character

Making Array From Page Elements in jQuery

Laravel's ORM sync with timestamps doesn't update timestamps

Do animations stop css changes after animation completion?