Log in
x
or
x
x
Register
x

or

normal How To VLOOKUP And Return Multiple Corresponding Values Horizontally In Excel?

More
3 months 1 week ago #1902 by LtSplinter
If i'm understanding you correctly, you are using VLOOKUPs to return values but there are instances where there is more than 1 occurrence of your lookup value and you want it to return all instances instead of just the first one.
=INDEX($B$28:$B$20014,AGGREGATE(15,3,(($F$27:$F$20014=$C$1)/($F$27:$F$20014=$C$1)*ROW($F$27:$F$20014))-ROW($F$27), ROWS($J$5:$J5)))
I'll try and explain it the best i can. I too wanted a lookup formula that would return all instances of that lookup value because i could have up to 10-15 of the same lookup value in my data set. I'm no expert in what everything means but i manage to get these complicated formulas to work so i will try to help best i can.
=INDEX($B$28:$B$20014
This is the column the formula will return a value from. My worksheet uses this formula in columns A-E. In each column i change this formula column to whatever one i need returned from my data set.
,AGGREGATE(15,3
I really won't be good at explaining the why here. The 15 and 3 are excel parameters that you can change, start typing this in and excel will show you a drop down list of possible choices, perhaps these 2 numbers aren't what you need but there are lots to choose from.
,(($F$27:$F$20014=$C$1)/($F$27:$F$20014=$C$1)*ROW($F$27:$F$20014))
$F$27:$F$20014 is where my data is located. Specifically column F is where my lookup value will be located. $C$1 is my lookup cell. Whatever i type in this cell, my formula will look for that value in F27:F20014 (This includes headers which i think is mandatory)
-ROW($F$27), ROWS($J$5:$J5)))
The -ROW($F$27) part removes that row (header row) from the return results (i think?) and the last part references a blank column with absolutely nothing in it, i believe this portion of the formula is what allows it to return multiple results, note that there is no absolute on the last $J5, this is so the formula can keep expanding to multiple rows.

Hopefully this is helpful to you, or anyone.
The following user(s) said Thank You: Excelfans

Please Log in or Create an account to join the conversation.

  • patrick.h.wessels@gmail.com
  • patrick.h.wessels@gmail.com's Avatar Topic Author
  • Offline
More
5 months 2 hours ago #1795 by patrick.h.wessels@gmail.com
Is it possible to to perform this function vertically instead of horizontally?  I have a list of data that is in a row and i need to utilize a lookup function to look up corresponding data and return these values in a vertical list not horizontal as shown in the link below. How to VLOOKUP and return multiple corresponding values horizontally in Excel?

 

Please Log in or Create an account to join the conversation.

Moderators: jaychivoExcelfansqiuhongkun