Lookup for the first occurrence
 

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,
--
Isabelle

Lookup for the first occurrence
 

Try VLOOKUP() instead

=vlookup("b17",A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria


"IPELLETIER" wrote:

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,
--
Isabelle

Lookup for the first occurrence
 

=vlookup(a1,sheet2!a:b,2,false)



IPELLETIER wrote:

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,
--
Isabelle

--

Dave Peterson

Lookup for the first occurrence
 

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.

--
Biff
Microsoft Excel MVP


"IPELLETIER" wrote in message
...
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,
--
Isabelle

Lookup for the first occurrence
 

Hmmm...

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"IPELLETIER" wrote in message
...
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,
--
Isabelle