# How to use the outcome of a formula as the value for Vlookup or another IF formula

Ok I will try to explain my issue effectively. I am making a GPA sheet in which the value out of 100 is computer in to a GPA value and then in to a letter.

In cell N5 i have the value of all their grades (formula: `=H3+H4+H5`)

Now in cell (j6) I have a formula which is giving them a number depending on the value calculated in N5 (Formula: `=IF(AND(N5>=60,N5<=63.999),"2.0",IF(AND(N5>=64,N5<=66.999),"2.25",IF(AND(N5>=67,N5<=69.999),"2.4",IF(AND(N5>=70,N5<=73.999),"2.5",IF(AND(N5>=74,N5<=76.999),"2.75",IF(AND(N5>=77,N5<=79.999),"2.9",IF(AND(N5>=80,N5<=83.999),"3.0",IF(AND(N5>=84,N5<=86.999),"3.25",IF(AND(N5>=87,N5<=89.999),"3.4",IF(AND(N5>=90,N5<=93.999),"3.50",IF(AND(N5>=94,N5<=96.999),"3.75",IF(AND(N5>=97,N5<=100),"4",IF(AND(N5<=59.999),"0")))))))))))))`

Still no problem... as the values I was looking for comes out (example 84.2 shows up as 3.25 as I wanted).

However here comes the problem.... I have tried to use the outcome in J6 to do Vlookup or another if formula, however excel does not seem to recognize the value in J6.

For example: `=VLOOKUP(j6,B3:C15,2,FALSE)`... this returns N/A however if I enter `=VLOOKUP(3.25,B3:C15,2,FALSE)` it gives me what im looking for. It seems that excel will not register the outcome of my formula as a number.

-
Sorry the last paragraph was meant to say For example: =VLOOKUP(j6,B3:C15,2,FALSE)... this returns N/A however if I enter =VLOOKUP(3.25,B3:C15,2,FALSE) it gives me what im looking for. It seems that excel will not register the outcome of my formula as a number. – Steven Nov 3 '13 at 13:10

By using quotes you are returning text values - remove the quotes, i.e.

`=IF(AND(N5>=60,N5<=63.999),2,IF(AND(N5>=64,N5<=66.999),2.25,IF(AND(N5>=67,N5<=69.999),2.4,IF(AND(N5>=70,N5<=73.999),2.5,IF(AND(N5>=74,N5<=76.999),2.75,IF(AND(N5>=77,N5<=79.999),2.9,IF(AND(N5>=80,N5<=83.999),3,IF(AND(N5>=84,N5<=86.999),3.25,IF(AND(N5>=87,N5<=89.999),3.4,IF(AND(N5>=90,N5<=93.999),3.5,IF(AND(N5>=94,N5<=96.999),3.75,IF(AND(N5>=97,N5<=100),4,IF(AND(N5<=59.999),0)))))))))))))`

....although you don't need all those ANDs - this should work for you

`=IF(N5<60,0,IF(N5<64,2,IF(N5<67,2.25,IF(N5<70,2.4,IF(N5<74,2.5,IF(N5<77,2.75,IF(N5<80,2.9,IF(N5<84,3,IF(N5<87,3.25,IF(N5<90,3.4,IF(N5<94,3.5,IF(N5<97,3.75,IF(N5<=100,4)))))))))))))`

or with LOOKUP

`=LOOKUP(N5,{0,60,64,67,70,74,77,80,84,87,90,94,97;0,2,2.25,2.4,2.5,2.75,2.9,3,3.25,3.4,3.5,3.75,4})`

-
you are amazing! thankyou so so much! – Steven Nov 3 '13 at 14:26

What is J6 formatted? Looks like it's probably text, which won't be useful if your vlookup is looking in numbers.

Try using `=value()` before your `if()` to convert text to number -

`=Value(IF(AND(N5>=60,N5<=63.999),"2.0",IF(AND(N5>=64,N5<=66.999),"2.25",IF(AND(N5>=67,N5<=69.999),"2.4",IF(AND(N5>=70,N5<=73.999),"2.5",IF(AND(N5>=74,N5<=76.999),"2.75",IF(AND(N5>=77,N5<=79.999),"2.9",IF(AND(N5>=80,N5<=83.999),"3.0",IF(AND(N5>=84,N5<=86.999),"3.25",IF(AND(N5>=87,N5<=89.999),"3.4",IF(AND(N5>=90,N5<=93.999),"3.50",IF(AND(N5>=94,N5<=96.999),"3.75",IF(AND(N5>=97,N5<=100),"4",IF(AND(N5<=59.999),"0"))))))))))))))`

Will that work in the vlookup?

-