VLOOKUP Difficulty

[Serge]

=IF(OR(A45="",C45=""),"",IF(ISNA(VLOOKUP(A45,SAE_NutWTable,2)),2,TRUE))*C45
the above formula is not giving the answer I am looking for.
In A45=0.625 (nut size)
In C45=95 (quantity)
In D45 is the above formula and my answer should be 7 lb but I'm getting 95
for answer.
The formula looks up a table named "SAE_NutWTable" in the "Bolt Weights"
sheet.
This table looks like below:
DIAMETER WEIGHT
0.500 0.038
0.625 0.073
0.750 0.119
0.875 0.190
1.000 0.283
1.125 0.403
1.250 0.543
1.375 0.730
1.500 0.943
Can someone help with this formula please?
Many thanks in advance
Serge

 

[TUNGANA KURMA RAJU]

modify your formula like this:
=IF(OR(A45="",C45=""),"",IF(ISNA(VLOOKUP(A45,SAE_NutWTable,2,FALSE)),2,TRUE))*C45

 

[JMB]

IF(ISNA(VLOOKUP(A45,SAE_NutWTable,2)),2,TRUE))*C45

Because this part of your formula evaluates to

TRUE*C45

Excel stores TRUE as 1, so

1*C45 = 95

BTW - It looks to me that since VLOOKUP is looking for an approximate match,
your formula will always return the false condition of your IF statement
(TRUE in this case).

Do you want an exact match for the lookup and if one is found return the
result of the lookup, otherwise return 2? If so, maybe change to

IF(ISNA(VLOOKUP(A45,SAE_NutWTable,2,FALSE)),2,VLOOKUP(A45,SAE_NutWTable,2,FALSE))*C45

 

[Serge]

Hello Tungana Kurma Raju,
I'm sorry to say that the change gave me the same answer as before.
Serge

 

[Serge]

Hello JMB,
I'm please to say that your formula is right on the marker.
It works great.
Many thanks.
Serge

 

[JMB]

You're welcome. Thanks for the feedback.

Hello JMB,
I'm please to say that your formula is right on the marker.
It works great.
Many thanks.
Serge