VLOOKUP Difficulty

=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

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

"Serge" wrote:

> =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
>

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" wrote:

> =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
>

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

"TUNGANA KURMA RAJU" wrote:

> modify your formula like this:
> =IF(OR(A45="",C45=""),"",IF(ISNA(VLOOKUP(A45,SAE_NutWTable,2,FALSE)),2,TRUE))*C45
>
> "Serge" wrote:
>
> > =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
> >

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

"JMB" wrote:

> 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" wrote:
>
> > =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
> >

You're welcome. Thanks for the feedback.

"Serge" wrote:

> Hello JMB,
> I'm please to say that your formula is right on the marker.
> It works great.
> Many thanks.
> Serge
>
> "JMB" wrote:
>
> > 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" wrote:
> >
> > > =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
> > >