how to use vlookup with dublicate values

abbas · Mar 20th, 2002, 07:13 AM

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 · Mar 20th, 2002, 08:21 AM

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 · Mar 20th, 2002, 09:24 AM

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

Yogi Anand · Mar 20th, 2002, 09:48 AM

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 · Mar 20th, 2002, 10:04 AM

many thanks Yogi!

Don C · Mar 20th, 2002, 10:37 AM

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.

Mar 20th, 2002, 09:48 AM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	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 __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 07:13 AM	#1
abbas New Member Join Date: Mar 2002 Location: Sabir Mamedov Posts: 3	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.

Mar 20th, 2002, 08:21 AM	#2
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	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.

Mar 20th, 2002, 09:24 AM	#3
abbas New Member Join Date: Mar 2002 Location: Sabir Mamedov Posts: 3	thank you Don, is there a shorter way i.e. you write the formula in d2, fill down and OK?

Mar 20th, 2002, 10:04 AM	#5
abbas New Member Join Date: Mar 2002 Location: Sabir Mamedov Posts: 3	many thanks Yogi!

Mar 20th, 2002, 10:37 AM	#6
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	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.