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