Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup is not returning the right value.
I have a spreadsheet with the following names defined:
Funtional No: 1 2 3 4 5 6 7 8 9 10 11 Functional Areas: Business Rules Case Entry Reports Submissions Templates and Narratives User Access Control Workflow E2B Configuration E2B File Creation E2B Receipt E2B Submission As I enter the functional Area in another sheet, I am using the lookup function to return me the Fucntional No: =LOOKUP(A1,Functional_Group,Functional_No) But, for some strange reason, some of them return 2: Business Rules 1 Case Entry 2 Reports 3 Submissions 4 Templates and Narratives 5 User Access Control 6 Workflow 11 E2B Configuration 2 E2B File Creation 2 E2B Receipt 2 E2B Submission 2 I am puzzled! What am I not getting? Venki |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup is not returning the right value.
LOOKUP *requires* that the lookup_vector be sorted in ascending order.
If your Funtional No's really are sequential from 1 to n: =MATCH(A1,Functional_Group,0) -- Biff Microsoft Excel MVP "vvenk" wrote in message ... I have a spreadsheet with the following names defined: Funtional No: 1 2 3 4 5 6 7 8 9 10 11 Functional Areas: Business Rules Case Entry Reports Submissions Templates and Narratives User Access Control Workflow E2B Configuration E2B File Creation E2B Receipt E2B Submission As I enter the functional Area in another sheet, I am using the lookup function to return me the Fucntional No: =LOOKUP(A1,Functional_Group,Functional_No) But, for some strange reason, some of them return 2: Business Rules 1 Case Entry 2 Reports 3 Submissions 4 Templates and Narratives 5 User Access Control 6 Workflow 11 E2B Configuration 2 E2B File Creation 2 E2B Receipt 2 E2B Submission 2 I am puzzled! What am I not getting? Venki |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup is not returning the right value.
Biff:
Thanks for the tip. When I sorted the functional_group, the Lookup works fine. Venki "T. Valko" wrote: LOOKUP *requires* that the lookup_vector be sorted in ascending order. If your Funtional No's really are sequential from 1 to n: =MATCH(A1,Functional_Group,0) -- Biff Microsoft Excel MVP "vvenk" wrote in message ... I have a spreadsheet with the following names defined: Funtional No: 1 2 3 4 5 6 7 8 9 10 11 Functional Areas: Business Rules Case Entry Reports Submissions Templates and Narratives User Access Control Workflow E2B Configuration E2B File Creation E2B Receipt E2B Submission As I enter the functional Area in another sheet, I am using the lookup function to return me the Fucntional No: =LOOKUP(A1,Functional_Group,Functional_No) But, for some strange reason, some of them return 2: Business Rules 1 Case Entry 2 Reports 3 Submissions 4 Templates and Narratives 5 User Access Control 6 Workflow 11 E2B Configuration 2 E2B File Creation 2 E2B Receipt 2 E2B Submission 2 I am puzzled! What am I not getting? Venki |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup is not returning the right value.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "vvenk" wrote in message ... Biff: Thanks for the tip. When I sorted the functional_group, the Lookup works fine. Venki "T. Valko" wrote: LOOKUP *requires* that the lookup_vector be sorted in ascending order. If your Funtional No's really are sequential from 1 to n: =MATCH(A1,Functional_Group,0) -- Biff Microsoft Excel MVP "vvenk" wrote in message ... I have a spreadsheet with the following names defined: Funtional No: 1 2 3 4 5 6 7 8 9 10 11 Functional Areas: Business Rules Case Entry Reports Submissions Templates and Narratives User Access Control Workflow E2B Configuration E2B File Creation E2B Receipt E2B Submission As I enter the functional Area in another sheet, I am using the lookup function to return me the Fucntional No: =LOOKUP(A1,Functional_Group,Functional_No) But, for some strange reason, some of them return 2: Business Rules 1 Case Entry 2 Reports 3 Submissions 4 Templates and Narratives 5 User Access Control 6 Workflow 11 E2B Configuration 2 E2B File Creation 2 E2B Receipt 2 E2B Submission 2 I am puzzled! What am I not getting? Venki |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup returning incorrect result | Excel Worksheet Functions | |||
Lookup returning one more than expected | Excel Worksheet Functions | |||
lookup returning several cells | Excel Discussion (Misc queries) | |||
Returning a blank cell rather then #N/A (Lookup) | Excel Discussion (Misc queries) | |||
Returning all values from a lookup - not just the first/last one | Excel Worksheet Functions |