Log in  or  Register  or

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

• LtSplinter
• • Offline
5 months 2 weeks 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

• Topic Author