Table Lookup

[Rod]

I have a spreadsheet that looks something like this:

Coverage
Amount AGE
0 - 40 41 - 45 46 - 50 51 - 60 61+
0 - 40,000 A A A A B
41,001 - 99,999 A A A B B
100,000 - 199,999 A A A F F
200,000 - 249,999 A A F G G
250,000 - 300 000 E F F G H
300,000 - 499,999 F F G G H
500,000 F F G G H
500,001 - 750,000 F G G G H
750,001 - 1,000,000 G G G H H
1,000,001 - 1,500,000 G G H H H
1,5,000,001 - 2,000,000 G H H H H
2,000,001 - and up H H H H H

A Procedure 1
B Procedure 2
C Procedure 3
D Procedure 4
E Procedure 5
F Procedure 6
G Procedure 7
H Procedure 8

ISSUE:
I need to be able to lookup the coverage amount range, (e.g. the coverage
could be 200,003), lookup the age (for example 43) and return what procedure
is to be followed (to complete the example 200,003 for a 43 year old should
return "Procedure 1".

Any specific help?

[Domenic]

Assumptions:

A4:B15 contains your coverage amount range
C3:G3 contains your age brackets
C4:G15 contains your data
A17:B24 contains your 'Procedure" table

Formula:

=LOOKUP(INDEX(C4:G15,MATCH(D17,A4:A15),MATCH(E17,{0,41,46,51,61})),A17:B2
4)

....where D17 contains the coverage amount, and E17 contains the age of
interest.

Hope this helps!

In article <046300DF-4C30-442B-A2E7-6A9A88E75782@microsoft.com>,
Rod <Rod@discussions.microsoft.com> wrote:

> I have a spreadsheet that looks something like this:
>
> Coverage
> Amount AGE
> 0 - 40 41 - 45 46 - 50 51 - 60 61+
> 0 - 40,000 A A A A B
> 41,001 - 99,999 A A A B B
> 100,000 - 199,999 A A A F F
> 200,000 - 249,999 A A F G G
> 250,000 - 300 000 E F F G H
> 300,000 - 499,999 F F G G H
> 500,000 F F G G H
> 500,001 - 750,000 F G G G H
> 750,001 - 1,000,000 G G G H H
> 1,000,001 - 1,500,000 G G H H H
> 1,5,000,001 - 2,000,000 G H H H H
> 2,000,001 - and up H H H H H
>
> A Procedure 1
> B Procedure 2
> C Procedure 3
> D Procedure 4
> E Procedure 5
> F Procedure 6
> G Procedure 7
> H Procedure 8
>
> ISSUE:
> I need to be able to lookup the coverage amount range, (e.g. the coverage
> could be 200,003), lookup the age (for example 43) and return what procedure
> is to be followed (to complete the example 200,003 for a 43 year old should
> return "Procedure 1".
>
> Any specific help?

[Rod]

The "Amount" Lookup is working well, however, the "Age" lookup is not. I
tried age 42 and above and it returns #N/A. The age row is as such:
D3 E3 F3 G3 H3 I3 J3 K3 L3 M3 N3 O3 P3
0 - 40 41 - 45 46 - 50 51 - 60 61+

Thoughts?


"Domenic" wrote:

> Assumptions:
>
> A4:B15 contains your coverage amount range
> C3:G3 contains your age brackets
> C4:G15 contains your data
> A17:B24 contains your 'Procedure" table
>
> Formula:
>
> =LOOKUP(INDEX(C4:G15,MATCH(D17,A4:A15),MATCH(E17,{0,41,46,51,61})),A17:B2
> 4)
>
> ....where D17 contains the coverage amount, and E17 contains the age of
> interest.
>
> Hope this helps!
>
> In article <046300DF-4C30-442B-A2E7-6A9A88E75782@microsoft.com>,
> Rod <Rod@discussions.microsoft.com> wrote:
>
> > I have a spreadsheet that looks something like this:
> >
> > Coverage
> > Amount AGE
> > 0 - 40 41 - 45 46 - 50 51 - 60 61+
> > 0 - 40,000 A A A A B
> > 41,001 - 99,999 A A A B B
> > 100,000 - 199,999 A A A F F
> > 200,000 - 249,999 A A F G G
> > 250,000 - 300 000 E F F G H
> > 300,000 - 499,999 F F G G H
> > 500,000 F F G G H
> > 500,001 - 750,000 F G G G H
> > 750,001 - 1,000,000 G G G H H
> > 1,000,001 - 1,500,000 G G H H H
> > 1,5,000,001 - 2,000,000 G H H H H
> > 2,000,001 - and up H H H H H
> >
> > A Procedure 1
> > B Procedure 2
> > C Procedure 3
> > D Procedure 4
> > E Procedure 5
> > F Procedure 6
> > G Procedure 7
> > H Procedure 8
> >
> > ISSUE:
> > I need to be able to lookup the coverage amount range, (e.g. the coverage
> > could be 200,003), lookup the age (for example 43) and return what procedure
> > is to be followed (to complete the example 200,003 for a 43 year old should
> > return "Procedure 1".
> >
> > Any specific help?
>

[Domenic]

That's because I assumed that each age bracket is contained in one cell.
But I see now that this is not the case.

From what I can tell from your original post, the data for each age
bracket is contained in Columns D, G, J, M, and P. If this is the case,
replace this part of the formula...

MATCH(E17,{0,41,46,51,61})

....with the following...

CHOOSE(MATCH(E17,{0,41,46,51,61}),1,4,7,10,13)

Does this help?

In article <75B10480-8DCD-4CED-A5EC-BD36AA71D388@microsoft.com>,
Rod <Rod@discussions.microsoft.com> wrote:

> The "Amount" Lookup is working well, however, the "Age" lookup is not. I
> tried age 42 and above and it returns #N/A. The age row is as such:
> D3 E3 F3 G3 H3 I3 J3 K3 L3 M3 N3 O3 P3
> 0 - 40 41 - 45 46 - 50 51 - 60 61+
>
> Thoughts?
>
>
> "Domenic" wrote:
>
> > Assumptions:
> >
> > A4:B15 contains your coverage amount range
> > C3:G3 contains your age brackets
> > C4:G15 contains your data
> > A17:B24 contains your 'Procedure" table
> >
> > Formula:
> >
> > =LOOKUP(INDEX(C4:G15,MATCH(D17,A4:A15),MATCH(E17,{0,41,46,51,61})),A17:B2
> > 4)
> >
> > ....where D17 contains the coverage amount, and E17 contains the age of
> > interest.
> >
> > Hope this helps!
> >
> > In article <046300DF-4C30-442B-A2E7-6A9A88E75782@microsoft.com>,
> > Rod <Rod@discussions.microsoft.com> wrote:
> >
> > > I have a spreadsheet that looks something like this:
> > >
> > > Coverage
> > > Amount AGE
> > > 0 - 40 41 - 45 46 - 50 51 - 60 61+
> > > 0 - 40,000 A A A A B
> > > 41,001 - 99,999 A A A B B
> > > 100,000 - 199,999 A A A F F
> > > 200,000 - 249,999 A A F G G
> > > 250,000 - 300 000 E F F G H
> > > 300,000 - 499,999 F F G G H
> > > 500,000 F F G G
> > > H
> > > 500,001 - 750,000 F G G G H
> > > 750,001 - 1,000,000 G G G H H
> > > 1,000,001 - 1,500,000 G G H H H
> > > 1,5,000,001 - 2,000,000 G H H H H
> > > 2,000,001 - and up H H H H H
> > >
> > > A Procedure 1
> > > B Procedure 2
> > > C Procedure 3
> > > D Procedure 4
> > > E Procedure 5
> > > F Procedure 6
> > > G Procedure 7
> > > H Procedure 8
> > >
> > > ISSUE:
> > > I need to be able to lookup the coverage amount range, (e.g. the coverage
> > > could be 200,003), lookup the age (for example 43) and return what
> > > procedure
> > > is to be followed (to complete the example 200,003 for a 43 year old
> > > should
> > > return "Procedure 1".
> > >
> > > Any specific help?
> >