But you're only using 9 combinations (A & B, High to Low)???
How about a worksheet formula suggestion:
I put your data in A1:C7
then in A10:A12, I put B's. (my header for the row)
In B10:B12, I put: High, Med, Low
In C8:E8, I put A's (my header for the column)
in C9:E9, I put Low, Med, High
then in C10, I put this array formula:
=INDEX($A$1:$A$7,MATCH(C$9&"--"&$B10,$B$1:$B$7&"--"&$C$1:$C$7,0))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
And then I dragged it across from C10 to E10
and then I dragged down from C10:E10 to C12:E12
And I got this:
a a a
low med high
b high #4 #N/A #2
b med #1 #3 #N/A
b low #N/A #6 #N/A
If I really cared about getting rid of the n/a's, I could modify my formula:
=IF(ISERROR(MATCH(C$9&"--"&$B10,$B$1:$B$7&"--"&$C$1:$C$7,0)),"--",
INDEX($A$1:$A$7,MATCH(C$9&"--"&$B10,$B$1:$B$7&"--"&$C$1:$C$7,0)))
(all one cell and still an array formula).
This gave me:
a a a
low med high
b high #4 -- #2
b med #1 #3 --
b low -- #6 --
And that's pretty close.
bill wrote:
Unfortuantely, column A is not numeric - they're sequential, representing a line item number - could be anything text. Thanks.
--
Dave Peterson