0
Undo
Votes
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.KYTOOLS LINK
0
Undo
Votes
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.
=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.
- Page :
- 1
There are no replies made for this post yet.