NEED HELP

[ernie]

i have three worksheets 'Front', 'CPC' and 'Declaration'.
In 'CPC' i have a list of codes in columnA then columns B, C, D, and E have
values (%) which are applicable according to the codes. each code in
worksheet 'CPC' have there values. for example:

columnA | B | C | D | E |
row1: C400 | 5% | 10% | 1.5% | 3% |
row2: C418 | 10% | 5% | 2% | 8% |

On worksheet 'Front' G27 contains the cpc codes. i need the cells J31, J32,
J33 and J34 to be linked to the columns on 'CPC' columnB C D E respectively.
so if G27 on 'Front' is C400 then J31=5%, J32=10%, J33=1.5% and J34=3%

if G27 is C418 J31 should now be 10%, J32=5%, J33=2% and J34=8%

i'm not sure how to do this.. but i assume in each of these cells (J31 J32
J33 J34) should have there individual formula to pick up the values depending
on which row the codes is in on worksheet 'CPC'.

i hope someone understands me. let me know if i gave enough info.
thanks in advance.

 

[pinmaster]

What you need is VLOOKUP, something like this:

in J31
=VLOOKUP($G27,CPC!$A$1:$E$100,COLUMN(B1),0)
copied across

HTH

Jean-Guy

 

[Michael]

Hi ernie. You can use the VLOOKUP function to accomplish what you want.
First, it is usually wise when building your data table to have row 1 as
labels for the data. In the sample I used in row 1 on CDC I typed in Column
A - Code and then in Columns B, C, D and E I typed Percent 1, Percent 2,
Percent 3, Percent 4. I started your data in row 2. Next on your front
sheet I entered the following formulas:
J31: =VLOOKUP($G$27,CPC!$A$2:$E$3,2,FALSE)
J32: =VLOOKUP($G$27,CPC!$A$2:$E$3,3,FALSE)
J33: =VLOOKUP($G$27,CPC!$A$2:$E$3,4,FALSE)
J34: =VLOOKUP($G$27,CPC!$A$2:$E$3,5,FALSE)
Please adapt the cell definition of the CPC matrix to fit your data range.
The $ makes the data table address and G27 absolute so that you can copy the
formulas. The 2,3,4,5 next to FALSE are the column numbers of the data,
starting from the one next to Column A in the CPC sheet. Finally, Column A
on CPC needs to be sorted ascending. HTH

 

[ernie]

it didnt work for me.
--
help a friend help you

What you need is VLOOKUP, something like this:

in J31
=VLOOKUP($G27,CPC!$A$1:$E$100,COLUMN(B1),0)
copied across

HTH

Jean-Guy