INDEX MATCH formula

[Susan]

I used the Lookup Wizard to create a formula which is
=INDEX('Penalty Table'!$A$7:$F$26, MATCH(1,'Penalty Table'!$A$7:$A$26,),
MATCH(2003,'Penalty Table'!$A$7:$F$7,)). This only works one time.

I am creating a form where the employee enters the contract year, the
practice code number and then hopefully the percentage from the penalty
table is
automatically filled in. There will be several lines where they will be
filling in this information for contract holders. How can I enter this
formula for multiple entries? I think it has something to do with using
Ctrl-Shift-Enter to commit the formula. I don't understand how to use this.

Susan

 

[Miguel Zapico]

To make the formulas work for more cell, change the hardcoded lookup values
on the MATCH formulas (1 and 2003 in this case) to cell references, like $A2
or B$5. You may need to play with the $ sign to match you specific layout,
to fix either rows or columns as appropiate.

Hope this helps,
Miguel.

 

[Susan]

Miguel,

The values 1 and 2003 as shown in my formula are not constant values. They
are just one example of where the data is pulled from a chart on another
worksheet page made up of practice codes which are numbered in rows (1-18)
and the columns are years 2002-2005. Where the row and column converge is
the percentage, which is the value being transferred to the cell containing
the formula. Do you know how I can get this to work with any combination of
code and year that might get put into a form??

Susan

 

[Bob Phillips]

As Miguel said, use something like

=INDEX('Penalty Table'!$A$7:$F$26, MATCH($A2,'Penalty Table'!$A$7:$A$26,),
MATCH(B$1,'Penalty Table'!$A$7:$F$7,))

play with the cells to get the correct value.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)