How to look up a value in a list and return multiple corresponding

1

123456789

I followed the instructions on the excel help page of how look up a value in
a list and return multiple corresponding values but somethings not right. I
have tried this numerous times in my excel spreadsheet and it does not return
any values let alone numerous values. No values will appear unless I go into
the insert menu and click function, but then here it will only give me the
smallest value, even when there is more than one. Please help asap! Thank
you.
 
P

Pete_UK

That's how VLOOKUP (and MATCH, HLOOKUP etc) works when you have
multiple matching values - it returns the first matching value in the
list.

There are ways around it. I like to set up in another column a unique
reference, made up from the value in the first column of your table
together with a sequential number (an occurrence number, if you like).
You can then use this as the lookup column, although you might have to
use INDEX/MATCH rather than VLOOKUP as the new unique reference may
not be in the left-most column of your table (which is where it needs
to be for VLOOKUP to work). Consider an example with a table like
this:

abc 12
abc 15
def 21
abc 19
xyz 13
def 25
abc 17

Put this formula in C1 and copy down:

=IF(A1="","",A1&"_"&COUNTIF(A$1:A1,A1))

which will result in the following in column C:

abc_1
abc_2
def_1
abc_3
xzy_1
def_2
abc_4

The underscore is not strictly needed, but you can see how a
sequential number has been added onto the values from column A, which
gives us our unique references. Now, suppose in E1 you enter the value
that you want to lookup, eg abc, and you want to return all the
corresponding values in column F. Put this in F1:

=IF(E$1="","",IF(ISNA(MATCH(E$1&"_"&ROW(A1),C:C,0)),"",INDEX(B:B,MATCH
(E$1&"_"&ROW(A1),C:C,0))))

and copy it down as far as you think you might need it (eg to F7),
with this result:

12
15
19
17

and 3 blank cells beyond it as there are no more repeats of abc.
Change E1 to def and see the numbers change accordingly.

Obviously, your own situation will be much bigger than this example,
but as you gave no details of what cells you currently use I can only
refer to the example layout - perhaps you can apply the formula to
your own data.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Look up two values in different columns and return matching value 5
look up a value from a range and return several corresponding valu 1
Finding Corresponding Cell Value 2
Look up to return multiple corresponding values 1
Look up one value and return multiple corresponding values 3
Look up one value and return multiple corresponding values 5
How to look up a value in a list and return multiple corresponding values 7
Lookup one value and return multiple corresponding values 4

Top