vlookup duplicate data?

[pleasehelpme]

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..?

 

[Derek]

Aladin
What if your data in column A is text rather than numbers, this is giving me an error return when I try it
regards
Derek

 

[codger]

OK, other solutions I know but this is how I approach these.

It needs a little knowledge of the data so is not entirely general but you can make it so. With the given example, where the numbers are small positive integers, it is relatively simple.

You have two ranges no doubt, call them numRange for the 20,39 etc. and textRange for the animals. The approach is to order them but introduce non-disturbing noise into that. So:

{=LARGE(numRange,{1;2;3;4;5;6})}

Gives you numbers, duplicates and all, but,

{=INDEX(TextRange,MATCH(LARGE(numRange+ROW(numRange)/100,{1;2;3;4;5;6}),numRange+ROW(numRange)/100,0))}

Does the text. By adding the 'ROW(numRange)/100' part you've simply broken up the equalities.

You can replace 100 by ROWS(numRange) if necessary and you can create a column of integers by {=ROW(OFFSET(INDIRECT("A1"),0,0,n))} for artitrary n.

 

[Aladin Akyurek]

On 2002-04-09 08:01, Derek wrote:
Aladin
What if your data in column A is text rather than numbers, this is giving me an error return when I try it
regards
Derek

The target system of formulas applies to the text case too. It requires modification (indicated in bold below) at those places where the data type of A is exploited.

Consider

{"Field1","Field2";
"xza","cat";
"sad","dog";
"xza","cow";
"sad","fox";
"ret","rat";
"sad","mouse"}

in A1:B7.

In D1 enter:

=MATCH(REPT("z",20),Sheet1!A:A)-ROW(1:1)

In D2 enter: sad [ a lookup value ]

In E2 enter:

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

In E3 enter and copy down:

=IF(AND(LEN($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)),"")

Here is what you get in the results area:

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

Aladin