Vlookup help please

[Korimint]

Why does this formula create and #N/A

=IF($A8="","",VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0))

But this one does not

=IF($A8="","",VLOOKUP($A8,'What needs made'!$C$2:$H$36,5,0))

Both are looking up a zero total, the second formula returns a zero, the first formula returns a "Blank space"

Do I have a formating error? Both are formated to be Numbers.

So confused!!

I am using excel 2010 & Windows 7

Thanks for the help!

 

[Dryver14]

what is in column 4 and 5 of the lookup table?

 

[Korimint]

what is in column 4 and 5 of the lookup table?

4 has a formula that returns a blank space, 5 has nothing for that particular item

 

[Dryver14]

ive just tried both senarios on excel 2007 and got the ame result
a 0 for blank and a blank for a formula cell.

May require another if statement, do you want to return the blank or the 0?

 

[Aladin Akyurek]

Why does this formula create and #N/A

=IF($A8="","",VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0))

But this one does not

=IF($A8="","",VLOOKUP($A8,'What needs made'!$C$2:$H$36,5,0))

Both are looking up a zero total, the second formula returns a zero, the first formula returns a "Blank space"

Do I have a formating error? Both are formated to be Numbers.

So confused!!

I am using excel 2010 & Windows 7

Thanks for the help!

Are you trying to say that:

a)

=IF($A8="","",VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0))

is expected to return a blank ("" or 0 a space char ?);

b)

=IF($A8="","",VLOOKUP($A8,'What needs made'!$C$2:$H$36,5,0))

is expected to return a 0.

But, with (a) you get #N/A, with (b) you don't.

Question: Does $A8 house the same value when you get #N/A and when you don't?

 

[Korimint]

ive just tried both senarios on excel 2007 and got the ame result
a 0 for blank and a blank for a formula cell.

May require another if statement, do you want to return the blank or the 0?

I would like it to return a 0 so my other calculations work

Thanks for the help!

 

[Korimint]

Are you trying to say that:

a)

=IF($A8="","",VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0))

is expected to return a blank ("" or 0 a space char ?);

b)

=IF($A8="","",VLOOKUP($A8,'What needs made'!$C$2:$H$36,5,0))

is expected to return a 0.

But, with (a) you get #N/A, with (b) you don't.

Question: Does $A8 house the same value when you get #N/A and when you don't?

I would like a) to return a 0 not a blank space

Yes $A8 houses the same value

 

[Dryver14]

=IF($A8="","",IF(VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0)="",0,VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0)))

there is probably a better way

 

[Korimint]

=IF($A8="","",IF(VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0)="",0,VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0)))

there is probably a better way

Thanks, that at least helps me understand what I need to think

Thanks again for the help!

 

[Aladin Akyurek]

I would like a) to return a 0 not a blank space

Yes $A8 houses the same value

=IF($A8="","",LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    VLOOKUP($A8,'What needs made'!$C$2:$H$36,4,0)))