If ?

[mbritt]

I am working on creating a formula to quantify if x>=100%, then the total points (deposited in the cell) will be "10" .... however, if x<100% then it will pull from a separate vlook up table (i.e. 99%=9, 98%=8, etc) .....

The formula I am currently using looks like:
=IF(G5>=100%,10,(IF(G5<=100%,(VLOOKUP(G5,Sheet1!$A$1:$B$100,2,TRUE)))))

... all is well if we score 100% ... but it seems the second phase is only seeing "0" as the value because it is putting in the value for the 0% cell.

Any suggestion?

 

[ExcelRyan]

Try use FALSE instead of TRUE in vlookup statement.

 

[VoG]

I think the second IF is redundant. Try

=IF(G5>=100%,10,VLOOKUP(G5,Sheet1!$A$1:$B$100,2,TRUE))

 

[mbritt]

Peter,
Thanks for the speedy reply, however your formula does the same thing, it reads the quantity as "0" and puts in the value as such.

Second thoughts? I have tried devaluing the numbers from % to decimal - it still reads the "0" in the vlookup.

Thanks,
Michele