Lookup for the first occurrence

  • Thread starter Thread starter IPELLETIER
  • Start date Start date
I

IPELLETIER

Hello,


I am looking for a value associated with the first occurrence of a specific
set. I am thinking using LOOKUP function, but it returns to me the lastest
occurrence.


Example:


1 NA 3000
2 NA 3001
3 B17 3002
4 B17 3003
5 B17 3004
6 B17 3005
7 B17 3006
8 B17 3007
9 B20 3008
10 B20 3009
11 B20 3010

What I want is the number associated with the 1st occurrence of the set B17,
i.e. 3002. Please do not suggest to order my data in inverse order.

Thank you in advance.
Best regards,
 
=vlookup(a1,sheet2!a:b,2,false)


Hello,

I am looking for a value associated with the first occurrence of a specific
set. I am thinking using LOOKUP function, but it returns to me the lastest
occurrence.

Example:

1 NA 3000
2 NA 3001
3 B17 3002
4 B17 3003
5 B17 3004
6 B17 3005
7 B17 3006
8 B17 3007
9 B20 3008
10 B20 3009
11 B20 3010

What I want is the number associated with the 1st occurrence of the set B17,
i.e. 3002. Please do not suggest to order my data in inverse order.

Thank you in advance.
Best regards,
Click to expand...
 
One way...

E2 = B17
F2 = 3002

Array entered** :

=INDEX(A2:A12,MATCH(1,IF(B2:B12=E2,IF(C2:C12=F2,1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Hmmm...

After reading the other replies I may have not understood what you wanted!
(or maybe I did!)
 
Back
Top