Lookup table that takes into account 2 values to give 1 output value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

redforest - 01 Dec 2006 08:49 GMT

Hi
I am wanting to create a lookup table that uses 2 values as an
assessment tool. The table looks at peoples perfomance (P) levels and a
percentage between each level to give a numerical value output. Cell B1
contains 'P Level' (input value, this ranges from P1 to P8). Cell B2
contains the percentage (input value). In columns G are the 'P level'
values, in column H are the percentages (the percentages are divided
into 5th's, between 1 and 20%, between 21 and 40%, between 41 and 60%,
between 61 and 80% and between 81and 100%. In column I are numerical
values. I want to create a lookup table that will take the P level
value (B1) and then the percentage value (B2) and give it a numerical
value. For instance a peson could be on level P4 with 64% and be given
a numerical value of 18.
Many thanks for any help

Reply to this Message

Pete_UK - 01 Dec 2006 09:45 GMT

I have just replied to you directly - basically, arrange your table as
2-D, with the P levels going down and the Percentages across, and use
MATCH twice to find the appropriate row and column, then INDEX to read
the value at the intersection.

Pete

> Hi
> I am wanting to create a lookup table that uses 2 values as an
[quoted text clipped - 10 lines]
> a numerical value of 18.
> Many thanks for any help

Reply to this Message

Bob Phillips - 01 Dec 2006 09:48 GMT

=INDEX(i1:i100,MATCH(1,(G1:G100=B1)*(H1:HG100=B2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi
> I am wanting to create a lookup table that uses 2 values as an
[quoted text clipped - 10 lines]
> a numerical value of 18.
> Many thanks for any help

Reply to this Message

RichardSchollar - 01 Dec 2006 12:10 GMT

You can use this variant of Bob's which isn't an array formula (so just
needs enter):

=INDEX(I1:I100,MATCH(1,INDEX((G1:G100=B1)*(H1:H100=B2),0),0))

Richard

> =INDEX(i1:i100,MATCH(1,(G1:G100=B1)*(H1:HG100=B2),0))
>
[quoted text clipped - 25 lines]
> > a numerical value of 18.
> > Many thanks for any help

Reply to this Message

Bob Phillips - 01 Dec 2006 14:37 GMT

Interesting that you should use a technique that you got from me in
response to my answer<G>

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> You can use this variant of Bob's which isn't an array formula (so just
> needs enter):
[quoted text clipped - 32 lines]
> > > a numerical value of 18.
> > > Many thanks for any help

Reply to this Message

RichardSchollar - 02 Dec 2006 09:29 GMT

Bob

Your post below confused the heck out of me and I was going to reply
explaining that somebody else had shown me that construct.... until, of
course, I realised you are that somebody else! I should complete the
circle and give the LOOKUP alternative too :-D

=LOOKUP(2,1/((G1:G100=B1)*(H1:H100=B2)),I1:I100)

But I do love your Index-within-an-Index formula ;-)

Best regards - and hope to see you at MrExcel more

Richard

> Interesting that you should use a technique that you got from me in
> response to my answer<G>
[quoted text clipped - 47 lines]
> > > > a numerical value of 18.
> > > > Many thanks for any help

Reply to this Message

Bob Phillips - 02 Dec 2006 09:38 GMT

I was also going to post the LOOKUP version, but thought I would also wait
and see if you did. The circle is completed <g>

Regards

Bob

> Bob
>
[quoted text clipped - 62 lines]
> > > > > a numerical value of 18.
> > > > > Many thanks for any help

Reply to this Message

Rate this thread: