k c

In one of my spread sheets it need to be able to look up a match to a formulas result. For example after a formula has been calculated I need to be able to find the corresponding answer from a chart:
c30*f30 = 4.6

2 1.0 1.2 1.3 1.4 1.5 1.6 to 3.0
4 3.1 3.2 3.3 3.4 3.5 3.6 to 5.0
7 5.1 5.2 5.3 5.4 5.5 5.6 to 8.0
11 8.1 8.2 8.3 8.4 8.5 8.6 to 12.0
* 12.0 >

I need the 4.6 to look up and post 4 as a pertenage. Would I use the vlookup or index or match?

This might help:

=SUMPRODUCT((A1:A4)*(B1:U4=C30*F30))/100 ... formatted as % ?

Ola Sandstrom


I understand the first 4 rows, but I'm not sure what "*12>" shall return and what to return in case of "4.6 not found".

I think I need to clarify my needs a little better. If the formula returns a number of 5.3 I need it to post the number that is in the right column #7. Any formula return greater than 12.0 need to post the astric * for non compliance.

Degree of Multiplication
Nonconformance Factor
1.0 > =/<3.0 2
3.1 > =/<5.0 4
5.1 > =/<8.0 7
8.1 > =/<12.0 11
> 12.0 *Nonconformance

5.3 x 7 = 37%

How about:

=IF(AND(C30*F30>=1,C30*F30<12),VLOOKUP(C30*F30,{1;2,3;4,5;7,8;11},2,1)*C30*F30/100,"*")

where the cell is formatted as %

Ola Sandstrom