Vlookup

[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

 

[Domenic]

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!

 

[Harry Bo]

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

 

[Norman Harker]

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

 

[Norman Harker]

Hi Harry!

You needed to note that the separator used was a ; not a ,

 

[Harry Bo]

Thanks Norman, never noticed that......too many sherbets!

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

 

[anb001]

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

 

[anb001]

Norman,
Thnks. That works

 

[sujeet]

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

 

[Peo Sjoblom]

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