# Thread: Need Help of Matrix (Excel 2003)

1. ## Need Help of Matrix (Excel 2003)

Hi Excel Experts,

I am back with my queries after a long time.

I am attaching an excel in which i need the formula in Sheet "Master Data" to be fixed on Col J.

There are 3 sheet in the file Matrix, Master Data, STI Matrix. In Master Data in Col H the formula check for ColA, F G and pick the value from Matrix Sheet and give me the out put. I need a similar formula in Col J. that will check the Col E & G and pick the value from STI Matrix and give me the output. I tried using the same formula from Col H by modifiying the same however i am not getting the desired out.

Thanks
Baiju

2. ## Re: Need Help of Matrix (Excel 2003)

=INDEX('STI Matrix'!\$B\$3:\$F\$17,MATCH(\$E2,'STI Matrix'!\$A\$3:\$A\$17,0),MATCH(\$G2,'STI Matrix'!\$B\$2:\$F\$2,0))

Copy down the column
Steve

3. ## Re: Need Help of Matrix (Excel 2003)

=INDEX('STI Matrix'!\$A\$3:\$F\$17,MATCH(\$E2,'STI Matrix'!\$A\$3:\$A\$17,0),MATCH(\$G2,'STI Matrix'!\$A\$2:\$F\$2,0))

in cell J2, then fill down?

4. ## Re: Need Help of Matrix (Excel 2003)

=INDEX('STI Matrix'!\$B\$3:\$F\$17,MATCH(\$E2,'STI Matrix'!\$A\$3:\$A\$17,0),MATCH(\$G2,'STI Matrix'!\$B\$2:\$F\$2,0))

or:

=INDEX('STI Matrix'!\$A\$3:\$F\$17,MATCH(\$E2,'STI Matrix'!\$A\$3:\$A\$17,0),MATCH(\$G2,'STI Matrix'!\$A\$2:\$F\$2,0))

in cell J2, then fill down?

<img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

5. ## Re: Need Help of Matrix (Excel 2003)

But did you create those formulas yourself...? Steve and I did. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

6. ## Re: Need Help of Matrix (Excel 2003)

Yes. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> using only only 3 keys <img src=/S/devil.gif border=0 alt=devil width=15 height=15>

7. ## Re: Need Help of Matrix (Excel 2003)

Thanks Hans, Steve the formula is working perfect.. and thanks Nathan for consolidating the same :-)

8. ## Re: Need Help of Matrix (Excel 2003)

HI Hans/Steve,

I have made some changes to my STI Matrix to Include Min, Default & Max %. Earlier matrix had only the Default % and the formula works perfect.

Is is possible with the new matrix have the Min STI% in Col J and Max % on Col L of the Master Data Sheet.

I have attached the new file with the updated Matrix

Thanks
Baiju

9. ## Re: Need Help of Matrix (Excel 2003)

I'd group the Min values together, the Default values together and the Max values together. That makes it easy to adjust the formulas.

By the way, the grades in the new table don't match those in the old table - instead of 2 you now have 2.1 and 2.2 but column E in Master Data still contains 2, not 2.1 or 2.2.

10. ## Re: Need Help of Matrix (Excel 2003)

I have attached the workbook with the changes I'd make. I've used the old grades with the new percentages.

11. ## Re: Need Help of Matrix (Excel 2003)

Thanks Hans,

Its working perfectly.. You had not changed the ranges for Max STI which i have done.

Thanks for the help

Regards
Baiju

12. ## Re: Need Help of Matrix (Excel 2003)

Sorry, forgot about that at the last moment, but as you have found it's not difficult to modify the formula.

13. ## Re: Need Help of Matrix (Excel 2003)

Hi Hans,

I did figure out that 2.1 & 2.2 Missing. I have fixed that as well

Thanks for the help

Regards
Baiju

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•