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

```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

```
 0
12/1/2006 8:49:15 AM
excel 39879 articles. 2 followers.

6 Replies
523 Views

Similar Articles

[PageSpeed] 8

```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

redforest wrote:

> 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

```
 0
pashurst (2576)
12/1/2006 9:45:57 AM
```=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)

"redforest" <riverxavier@hotmail.co.uk> wrote in message
> 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
>

```
 0
bob.NGs1 (1661)
12/1/2006 9:48:14 AM
```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 wrote:

> =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)
>
> "redforest" <riverxavier@hotmail.co.uk> wrote in message
> > 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
> >

```
 0
12/1/2006 12:10:30 PM
```Interesting that you should use a technique that you got  from me in

--
HTH

Bob Phillips

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

"RichardSchollar" <richardschollar@hotmail.com> wrote in message
> 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 wrote:
>
> > =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)
> >
> > "redforest" <riverxavier@hotmail.co.uk> wrote in message
> > > 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
> > >
>

```
 0
bob.NGs1 (1661)
12/1/2006 2:37:09 PM
```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 wrote:
> Interesting that you should use a technique that you got  from me in
>
> --
>  HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "RichardSchollar" <richardschollar@hotmail.com> wrote in message
> > 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 wrote:
> >
> > > =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)
> > >
> > > "redforest" <riverxavier@hotmail.co.uk> wrote in message
> > > > 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
> > > >
> >

```
 0
12/2/2006 9:29:04 AM
```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

"RichardSchollar" <richardschollar@hotmail.com> wrote in message
> 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 wrote:
> > 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" <richardschollar@hotmail.com> wrote in message
> > > 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 wrote:
> > >
> > > > =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)
> > > >
> > > > "redforest" <riverxavier@hotmail.co.uk> wrote in message
> > > > > 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
> > > > >
> > >
>

```
 0
bob.NGs1 (1661)
12/2/2006 9:38:39 AM

Similar Artilces:

Zip Codes #2
Help...When I format a zip code file it appears in the field to show the "0" at the beginning of the field but it does not show when I print. The only work around is change it to text. I thought a zip code would recognize it as a zero. any idears.... -- deanf@johnsbyrne.com Hi Dean excel doesn't know what you've entered into a cell, it only recognises the "type" of data (text, number, date etc) .. so it won't know that you've entered zip codes and therefore won't "treat" them in a special way. However, i'm interested in HOW you for...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Renaming columns #2
I'd like to rename the A B C D at the top to the name of my columns, o at least leave the column headings at the top of my screen. Does anyone know how -- Message posted from http://www.ExcelForum.com Tools>Options>View, uncheck "Row and column headers". You can insert a row and a column with your own names, but they will not be recognized in formulas the same way as "A1". But you can define row and column names and use the intersection as an address in a formula. The intersection operator is a space. So if you defined the name "Material" for column ...

Problem with Null value elimintation criteria
Access 2007 on Vista. I'm building a simple append query to add missing records to a table. It examines a list of entries, identifies which are not in the destination table, and adds them. Simple thus far. The problem comes when I add a criteria to the source side to ensure no blank entries are appended. Here's the SQL I'm trying to use: INSERT INTO tblAgents ( AgentID, AgentName ) SELECT qryAgentsImport.RecAgentID, First(qryAgentsImport.RecAgentName) AS FirstOfRecAgentName FROM tblAgents RIGHT JOIN qryAgentsImport ON tblAgents.AgentID = qryAgent...

cannot open secondary email account
We just restored exchange 2000 database and everything went fine. Our problem is our XP users using Outlook XP cannot access their secondary mailbox. I can open the mailbox using windows 98 with outlook 2000 but I cant open the folders with XP Pro using Outlook XP. I get the following error: Unable to expand the folder. The set of folders could not be opened. The Information store could not be opened. Please help Thanks. ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

install crm 1.2 db
i had proplem installing cm in win 2k server new instaltion when i am trying to ad new database file in the instaltion give me an error he the password in sql is not set right i did not setup any password yet and how can i do so i just instal from the cd only please help ...

Hi to all. I have this scenario: Exchange Adm. Group 1 with one Exchange 5.5 and one Exchange 2003 Exchange Adm. Group 2 one exchange 2003 Exchange Adm. Group 3 one exchange 2003 I had installed ADC in the exchange 2003 in Exchange Adm. Group 2. The service crashed, and i couldn’t start it any more. I reinstalled de service, but now I don’t have any connections agreements so I can’t replicate with the exchange 5.5. When I try to run the connections agreements wizard he doesn’t detect any exchange 5.5 in the Adm. Groups 2 and 3 and I can’t finish. I can’t create manually the Configurati...

Cumulative Total #2
I am trying to create a running total of sales objective achievement. For example, If in August the objective is 20 and I actually sold 25, then I am at positive 5. Then in Sept, my objective is 30 and I sold 20, I am now at negative 5. I do not want the number to change in my monthly column, but I would like for it to update in my fiscal year column based upon sales for each month. Is it possible to do this and if so how? I don't exactly understand if you want the running total to be alongside the monthly totals in a separate column. Labels in row1 Columns A = Date (Month) B =...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Can't change account ownership
We recently had a employee leave the company and a number of accounts were assigned to him. His userid has since been removed from Active Directory and now CRM won't let me to reassign the ownership of any of those accounts. I get an error stating "The selected object could not be found. Verify that the object exists in both the database and Active Directory." Is there any way to force changes in the ownership? If you contact MBS support, there is a hotfix that will allow you to do this. Matt Parks MVP - Microsoft CRM ---------------------------------------- ------------...

exchange 2003 and blackberry #2
I am having a very strange issue. I am using blackberry handheld to receive emails from exchange. After I receive an email and read it. if i go back and use outlook web access or outlook on rpc over http and click to open any message, I get all emails again on the blackberry which i have already. The issue only happens when I use the webmail or outlook RPC. that i receive the emails again on my blackberry. Blackberry says it has no known issues of this kind . Running exchange server 2k3 on win2k3 standard edition. Pls help thanks kashi <kashi@discussions.microsoft.com> wrote: &...

Copy whole table into one cell
Office Excel 2003 on Windows XP I'm trying to create a report-maker with excel... ugh! Anyways, there is a template that users are filling out saying whether something is defected or not... that's not important, the important part is, that I'm having VBA go though the table (using a button after it is filled out) and deleting the rows that aren't defected... in other words, if there are blank cells in column C (for example), the whole row in which that blank cell is located is deleted. I'll never have more than 40 rows and 5 columns in the table so I don'...

Convert VC6.0 CString::Init() code to VC7.1.
How to convert the following code to VC7.1? // Need to declare a descendant of CString to allow access to the Init() member class CStackString : public CString { public: CStackString & operator=(CString const &s); CStackString & operator=(CStackString const &s); using CString::Init; // Make public }; inline CStackString & CStackString::operator=(CString const &s) { CString::operator=(s); return *this; } inline CStackString & CStackString::operator=(CStackString const &s) { CString::operator=(s); return *this; } class CStackValue...

Merging two tables into one table
Can anyone help me with this problem, I have been trying umpteen different ways of doing this with SQL (Which I don't entirely understand...) I want to merge the contents of two tables, fields into one set of consolidated fields but in a particular order. Namely, Run_No and OrderSeq. This is the SQL: SELECT A.Run_No, B.Run_No, A.Point_ID, B.Point_ID, A.OrderSeq, B.OrderSeq FROM tbl_Points AS A INNER JOIN tbl_Points AS B ON (A.Run_No+1=B.Run_No) AND (A.OrderSeq-9=B.OrderSeq); I want to merge the first 9 records of A.Run_No with the first 9 records of B.Run_No, along with their cor...

HQ
Currently we have an RMS implementation, which consists of an HQ and seven remote stores connected to it. I would like to know if it is possible to connect another database to RMS HQ. By this, I do not mean another remote store, but a new database for a store that has nothing to do with the other seven. I need another database because the new stores’ data is not to be ‘seen’ by the other remote stores’ users. To connect RMS HQ to this new database we can use different login credentials. Obviously all rms software used is same version, 2.0. Thanks, Nick Couple of issues... 1) I believ...

Open Access Database with VBA #2
I want to open a specific Access DB from and Excel button. I tried a previous sugestion in this forum and It would bring up access but not the file. Here is what I have: (I'm so colse to being happy!) Sub test() On Error Resume Next Set ac = GetObject(, "Access.Application") If ac Is Nothing Then Set ac = GetObject("", "Access.Application") ac.OpenCurrentDatabase "c:\data\temp\temp.mdb" ac.UserControl = True End If AppActivate "Microsoft Access" End Sub User Error....It works "Bubba" wrote: > I wa...

linking tables from different Access templates?
Hello I have been playing with Access 2007 and am at the stage of almost being ready to ditch the "toy" database and prepare the real thing. I have been working on a database set up for us which has worked well but is showing its limitations (and highlighting mine!) I like the look of the Access 2007 Contacts and Events templates which, with some tweaking, could work well for us. My question is - is it possible to link the Contacts table with the Events table created in the templates? At the moment, I have an Events table (which includes various information abo...

Table properties
Is there a way to determine what the cell height is in Pub 2002? I know I can change it by dragging but I'm rather anal - I want to make all of them the same. -- The problem with resting on your laurels is that eventually you are sitting on dead branches. JoAnn What you do is select the whole table and say enter font size 16. Now when you go to make the Table smaller, the cells will only go to that point size. Consequently they will now all be the same height. You can now select the whole table again and change it to the point size of the font you want to use. Don't forget ...