help needed with vlookup?

[wylie88]

Im useing this formula in my system and when there is nothing in B5 it just comes up as NA. is there any way to stop this from happening?


=VLOOKUP(B5,'Product database'!C2:E24,3,0)

thanks

 

[pwchad]

=if(iserror(VLOOKUP(B5,'Product database'!C2:E24,3,0)),0,VLOOKUP(B5,'Product database'!C2:E24,3,0))

If you change the 0 for "", it will leave the cell blank.
Or you can put in text to identify errors "Missing"

 

[masim]

try this:

=if(isna(VLOOKUP(B5,'Product database'!C2:E24,3,0)),"",(VLOOKUP(B5,'Product database'!C2:E24,3,0)))

Regards
Asim

 

[Aladin Akyurek]

Im useing this formula in my system and when there is nothing in B5 it just comes up as NA. is there any way to stop this from happening?


=VLOOKUP(B5,'Product database'!C2:E24,3,0)

thanks

If C2:E24 is sorted on C2:C24 in ascending order...

=IF(B5 < INDEX($C$2:$C$24,1,1),"",LOOKUP(B5,'Product database'!$C$2:$C$24)=B5,LOOKUP(B5,'Product database'!$C$2:$E$24))

If C2:E24 is not sorted on C2:C24...

D5:

=IF(B5<>"",VLOOKUP(B5,'Product database'!$C$2:$E$24,3,0),"")

C5:

=IF(ISNA(D5),"",D5)