Vlookup

  • Thread starter Thread starter anb001
  • Start date Start date
A

anb001

I have the following function in a worksheet:

=VLOOKUP(C4;'Waived Pivot'!$A$1:$B$100;2;0)

If there nothing to look up, it returns value: #N/A
Is it possible to get e.g. "-" returned in stead (without quotes).

Any help appreciated.

Thank
 
Hi,

Try,

=IF(ISNA(VLOOKUP(C4;'Waived
Pivot'!$A$1:$B$100;2;0)),"-",VLOOKUP(C4;'Waived Pivot'!$A$1:$B$100;2;0))

Hope this helps!
 
=If(iserror(VLOOKUP(C4;'Waived Pivot'!$A$1:$B$100;2;0),"",(VLOOKUP(C4;'Waived Pivot'!$A$1:$B$100;2;0))
 
Hi anb100

Wrap your formula with a test for #N/A

=IF(ISNA(VLOOKUP(C4;'Waived
Pivot'!$A$1:$B$100;2;0));"-";VLOOKUP(C4;'Waived
Pivot'!$A$1:$B$100;2;0))
 
Thanks Norman, never noticed that......too many sherbets!

Norman Harker said:
Hi anb100

Wrap your formula with a test for #N/A

=IF(ISNA(VLOOKUP(C4;'Waived
Pivot'!$A$1:$B$100;2;0));"-";VLOOKUP(C4;'Waived
Pivot'!$A$1:$B$100;2;0))
Click to expand...
 
I have tried to use function as entered, but Excel tells me that ther
is a problem with flwg part:

,"",

Do not know whats wrong, though
 
One of the most easiest formula to remove the #NA value i.e Try thi
function


=IF(COUNTIF(A1:B11,E1),VLOOKUP(E1,A1:B11,2,0),"0")

It will return the Zero value when it will not find any value.


Thanks,

Sujee
 
A couple of problems, "0" is not zero but a text representation of zero,
countif won't make any difference between numbers and text while vlookup
will,
it's better to use =IF(ISNA(MATCH(E1,A1:A11,0)),0,VLOOKUP(
of course if the lookups are all text you can use countif with confidence
but only the first column
A1:A11 not A1:B11

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom
 
Back
Top