how to use vlookup with dublicate values

[abbas]

in my worksheet i have 2 columns with data:
column A (a2:a83)--- customer names;
column B (b2:b83)--- number of boxes (coffee)they received

in some cells in column B there are the same numbers
(i.e. B13,B28,B32,B45,B68 = 750)
i want vlookup 750 in column B and return all the company
names which are in column A -- in cells D2:D6. i used
vlookup function but without any success, because vlookup
returns only first value.

Thank you.

 

[Don C]

You are right that vlookup will not bring back all occurrances.

If you are willing to sort the list, there are two options:

1. is the obvious sort on the number of boxes and all customers will group by the number of boxes.

2. If you have a specific number that you are looking for, you could add in column C
=IF(A2="750","Hit","")
and copy the formula down to all records.

This would display the word "Hit" for all customers with 750 boxes. Sort on column C and all the "Hit"s will group together.
The 750 in the formula could be a reference to a cell where you enter the number you are seeking.

If the order of the list is important, add a column which numbers the rows as you now have them and you can always sort on this column to return to the original order.

 

[abbas]

thank you Don,
is there a shorter way i.e. you write the formula in d2, fill down and OK?

 

[Yogi Anand]

Hi Abbas and Don:
Why not use Data|Filter technique to filter records with the desired value, in this case 750 in column B. Let us say I have some entries in cells A1 through A9, and numbers like 750 and more in cells B1 through B9, the example below shows original data and then the filtered data

original data
CloA ColB
A2 120
A3 750
A4 950
A5 750
A6 315
A7 317
A8 750
A9 215

filtered data
CloA ColB
A3 750
A5 750
A8 750

HTH

 

[abbas]

many thanks Yogi!

 

[Don C]

<is there a shorter way i.e. you write the formula in d2, fill down and OK?

Yes, you'd only have to key the formula once and then fill down. If you reference a cell where the value of interest is keyed, be sure that the formula uses "$c$r" to make the reference absolute.