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

[redforest]

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

 

[Pete_UK]

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

 

[Bob Phillips]

=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.

--
HTH

Bob Phillips

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

 

[RichardSchollar]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[RichardSchollar]

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

 

[Bob Phillips]

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