Substituting values to an array to compute for another value

[xmoore]

Hi,

Not sure how to go about this table problem
We have defined categories for each of the "fruits" -ie. category A,B,C,D
Each category represents a value multiplier - ie. A - 1/ B - 2/ C - 4 /D - 8

On another table, we plot the categories on each rows. The idea is to substitute the categories with number and multiply the same to the the perceived number of fruits

Enclosed herein is a sample worksheet

Hope you could provide some ideas. Unfornately, macro is not option right now due to maintainance.
thanks,

[DBY]

Hello
Perhaps the following Sumproduct formula might work:

Please Login or Register  to view this content.

Copied down alongside your 'Perceived # of Fruit' table.

Hope this helps.
DBY

[xmoore]

Sorry for the late reply.. it worked great... thanks....

[Ghozi Alkatiri]

at H3
=INDEX($B$12:$B$15,MATCH(LOOKUP("z",$B3:$G3,$B3:$G3),$A$12:$A$15,0))*INDEX($B$2:$G$2,MATCH(LOOKUP("z",$B3:$G3,$B3:$G3),B3:G3))

copy down