Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup last occurrence | Excel Discussion (Misc queries) | |||
nth occurrence of MATCH | Excel Worksheet Functions | |||
V Lookup 2nd Occurrence | New Users to Excel | |||
find last occurrence | Excel Worksheet Functions | |||
occurrence (last) reference | Excel Discussion (Misc queries) |