vlookup duplicate data?

pleasehelpme

New Member
Joined
Apr 7, 2002
Messages
5
If I have 2 columns as follows:

20 cat
39 dog
40 cow
39 fox
50 rat
39 mouse

Is there a way I can lookup "39" and return the first 39=dog and the next 39=fox and the next 39=mouse etc..?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Change the reference of VLOOKUP's table_array to exclude the previously found item.
 
Upvote 0
I am not sure if this helps you, but you may be able to use "AutoFilter" under the menu...

Data
Filter
Autofilter

This would allow you to show only the items that have "39" in the first column.

The only other idea that I have is to write some VBA code to ask the user for a value like "39" and then have the VBA code find all cells in column A that have "39" and write the results in some other cells. This should not be too dificult.

I hope this helps at least a little.

David
 
Upvote 0
On 2002-04-08 09:21, pleasehelpme wrote:
I was hoping I could keep the table array constant. Is there another way around this problem?

You could setup your table array like...

{20,"cat",1
;39,"dog",2
;40,"cow",3
;39,"fox",4
;50,"rat",5
;39,"mouse",6}

...and enter the array formula...

{=VLOOKUP(39,OFFSET(table_array,F1,),{2,3},0)}

...into cells E2:F2 (leaving F1 blank) and copying down to cells E4:F4.

Note 1: Substitute an absolute cell reference "table_array"

Note 2: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.
This message was edited by Mark W. on 2002-04-08 09:33
 
Upvote 0
On 2002-04-08 09:03, pleasehelpme wrote:
If I have 2 columns as follows:

20 cat
39 dog
40 cow
39 fox
50 rat
39 mouse

Is there a way I can lookup "39" and return the first 39=dog and the next 39=fox and the next 39=mouse etc..?

Lets say that A1:B7 houses the sample data you provided, with labels added:

{"Field1","Field2";
20,"cat";
39,"dog";
40,"cow";
39,"fox";
50,"rat";
39,"mouse"}

In D1 enter:

=MATCH(9.99999999999999E+307,Sheet1!A:A)-ROW(1:1)

This formula computes the number of actual data records in the data range dynamically.

In D2 enter: 39 [ which is your lookup value ]

In E2 enter:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")

In E3 enter and copy down until no more retrieval occurs, associated with the value in D2:

=IF(AND($D$2,COUNTIF(OFFSET($A$2,0,0,$D$1,1),$D$2)>COUNTA($E$2:E2)),INDEX(OFFSET($B$2,MATCH(E2,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),MATCH($D$2,OFFSET($A$2,MATCH(E2,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),0)),"")

This is what you're going to see in the results area:

{6,"";
39,"dog";
"","fox";
"","mouse"}


Aladin
 
Upvote 0
Thanks for that Aladin,
I am most impressed, however there are 2 points that may be of use:

Point 1/ correct me if I am wrong, but should part of your last message have read:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$1,2),2,0),"")

rather than:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")


Point 2/Is it possible to possible to overcome the problem of having duplicate data in "Field 2". For example, if we add on to the bottom of the existing data:

39, "rat"
39, "fox"

The results do NOT give:
{6,"";
39,"dog";
"","fox";
"","mouse";
"","rat";
"","fox"}

but instead give:
{6,"";
39,"dog";
"","fox";
"","mouse";
"","rat";
"","mouse"}

Many thanks
Nick
 
Upvote 0
Hi


If you have a look here: http://www.ozgrid.com/VBA/TwoColLkUp.htm you will find this UDF with full instructions.


Code:
Function FindNth(Table As Range, Val1 As Variant,Val1Occrnce As Integer, _
                  Val2 As Variant,Val2Col As Integer, ResultCol As Integer)

'''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com

'Finds the N'th value in the first Column of a table that has a stated _
 value on the same row in another Column.
'''''''''''''''''''''''''''''''''''''''

Dim i As Integer
Dim iCount As Integer
Dim rCol As Range

	For i = 1 To Table.Rows.Count
		If Table.Cells(i, 1) = Val1 And _
			Table.Cells(i, Val2Col) = Val2 Then
			iCount = iCount + 1
		End If

		If iCount = Val1Occrnce Then
			FindNth = Table.Cells(i, ResultCol)
			Exit For
		End If
	Next i
End Function
 
Upvote 0
Hi There

There is a very simple solution to this type of problem.

There are many elaborations you can make but the basic idea is this:
You insert a column with a formula which sequences the item you want to list.
You then do a vlookup on this column. Try this on your example

Put your numbers in Column B starting with B2 (keep A1 & A2 blank)
Put your names (cat, dog etc) in Column C starting with C2
In A2 paste this formula and scroll down =IF(B2=$E$1,1+COUNT($A$1:A1),"")
In E2 paste this formula and scroll down =VLOOKUP(ROW(E1),A:C,3,FALSE)
Now type 39 in cell E1 and the names associated with 39 will list below

If you want you can modify the formula so that #N/A does not show. Just change the formula in E2 to
=IF(COUNT(A:A)>=ROW(E1),VLOOKUP(ROW(E1),A:C,3,FALSE),"")

It is also possible, with modification, to obtain the answers from data in another sheet

Regards

Derek
This message was edited by Derek on 2002-04-09 07:39
 
Upvote 0

Thanks for that Aladin,
I am most impressed,


Thanks. It's mutual. Happy to see that you understood the system.

however there are 2 points that may be of use:

Point 1/ correct me if I am wrong, but should part of your last message have read:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$1,2),2,0),"")

rather than:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")


Yep. Just visually confounded D1 & D2. I should have put that MATCH formula in C2 to avoid that visual trap..


Point 2/Is it possible to possible to overcome the problem of having duplicate data in "Field 2". For example, if we add on to the bottom of the existing data:

39, "rat"
39, "fox"

The results do NOT give:
{6,"";
39,"dog";
"","fox";
"","mouse";
"","rat";
"","fox"}

but instead give:
{6,"";
39,"dog";
"","fox";
"","mouse";
"","rat";
"","mouse"}


That's right. The formulas assume the uniqueness of values in column B. The addition boils down to having duplicate records. You could eliminate such duplicate records using Advanced Filter.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top