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

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

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

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