Getting Excel to print the value of a cell if true or 'Doesn't exist' if there's no match

[videozvideoz]

Hi There,

I have the following formula:

=IF(VLOOKUP(B2,Plan!C$4:C$50,1,FALSE)=B2,B2,"Doesn't exist")

In B2 is "Craig Derry". The IF statement does a vlookup on a set of cells and if it matches B2 it prints the contents of B2 (in this case "Craig Derry") but if there is no match I get '#N/A'. I don't want it to say '#N/A', i'd like it to display 'Doesn't exist'. Can ISERROR help? If so I cannot get it to work.

Any help appreciated.

Thanks,

VideozVideoz

 

[AlphaFrog]

Try something like this...
=IF(COUNTIF(Plan!C$4:C$50,B2)>0,B2,"Doesn't exist")

 

[videozvideoz]

Excellent AlphaFrog - thanks for that. Whilst I've got you how can I prevent this formula from returning '#N/A' when something doesn't exist? I want it to say something of my choice. eg '0' or 'doesn't exist'

=VLOOKUP(B4,Order!$A$2:$C$11030,3,FALSE)

Thanks

 

[AlphaFrog]

Try something like...

=IF(ISERROR(VLOOKUP(B4,Order!$A$2:$C$11030,3,FALSE)), "Doesn't exist", VLOOKUP(B4,Order!$A$2:$C$11030,3,FALSE))

 

[T. Valko]

Excellent AlphaFrog - thanks for that. Whilst I've got you how can I prevent this formula from returning '#N/A' when something doesn't exist? I want it to say something of my choice. eg '0' or 'doesn't exist'

=VLOOKUP(B4,Order!$A$2:$C$11030,3,FALSE)

Thanks

What version of Excel are you using?

If you're using Excel 2007 or later:

One of these...

=IFERROR(VLOOKUP(B4,Order!$A$2:$C$11030,3,0),"Doesn't Exist")

=IFERROR(VLOOKUP(B4,Order!$A$2:$C$11030,3,0),0)

 

[videozvideoz]

Thanks AlphaFrog, but instead of "Exists" what if I want it to display the value of the VLOOKUP?
T.Valko - I'm using 2003 un4tch
-------------------------------------------------------------------------------------------------
AlphaFrog - ignore that, i've done it:

=IF(ISERROR(VLOOKUP(B2,Order!$A$2:$C$11030,3,FALSE)), "Doesn't exist", VLOOKUP(B2,Order!$A$2:$C$11030,3,FALSE))

 

[AlphaFrog]

Replace "Exists" with the VLOOKUP formula. Read the edited #4 thread again.

 

[T. Valko]

Thanks AlphaFrog, but instead of "Exists" what if I want it to display the value of the VLOOKUP?

T.Valko - I'm using 2003 un4tch

What type of data does the lookup normally return? Is it text? Numeric? Could be either?

 

[videozvideoz]

Replace "Exists" with the VLOOKUP formula. Read the edited #4 thread again.

Yep, done that one now - I corrected my previous post once i'd sussed it out.

Next query... I have the following formula (input as an array):

{=IF(ROWS(C$2:C3)>B$4,"",INDEX(Values,SMALL(IF(Names=B$1,ROW(Names)),ROWS(C$2:C3))))}

The range 'Names' makes reference to the 1st column below and the range 'Values' makes reference to the 2nd column below:


Craig 123
John 234
Dan 345
Craig 876
John 765
Dan 654
Craig 466


In B4 there is a COUNT of the number of the name in B1 from the 'Names' range [=COUNTIF(Names,B1)] It looks up the name entered in B1 and returns the corresponding values so that it displays the following on my front sheet:

Craig 123
Craig 876
Craig 466

My question is, how can I make this formula to 'skip over' a certain entry and move onto the next? For example, lets say i didn't want it to pick up 876 so my front sheet would just display:

Craig 123
Craig 466

Thanks. Hope i've explained it well enough

 

[T. Valko]

Yep, done that one now - I corrected my previous post once i'd sussed it out.

Next query... I have the following formula (input as an array):

{=IF(ROWS(C$2:C3)>B$4,"",INDEX(Values,SMALL(IF(Names=B$1,ROW(Names)),ROWS(C$2:C3))))}

The range 'Names' makes reference to the 1st column below and the range 'Values' makes reference to the 2nd column below:


Craig 123
John 234
Dan 345
Craig 876
John 765
Dan 654
Craig 466


In B4 there is a COUNT of the number of the name in B1 from the 'Names' range [=COUNTIF(Names,B1)] It looks up the name entered in B1 and returns the corresponding values so that it displays the following on my front sheet:

Craig 123
Craig 876
Craig 466

My question is, how can I make this formula to 'skip over' a certain entry and move onto the next? For example, lets say i didn't want it to pick up 876 so my front sheet would just display:

Craig 123
Craig 466

Thanks. Hope i've explained it well enough

Is there some kind of characteristic that relates to 876 that we can use to tell the formula to bypass it?

We could just say "find all values that are not 876" but I'm guessing there's more to it.

=IF(ROWS(C$2:C3)>B$4,"",INDEX(Values,SMALL(IF(Names=B$1,IF(Values<>876,ROW(Names))),ROWS(C$2:C3))))