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

 

[videozvideoz]

Hi Biff,

Nope, it's just that value I didn't want! I can see how you've amended the formula so i will give it a try now...

Thanks in advance!

 

[AlphaFrog]

Here's my go at it for what it's worth.

=IF(ROWS(C$2:C3)>B$4,"",INDEX(Values,SMALL(IF([COLOR="Red"](Names=B$1)*(Values<>876)[/COLOR],ROW(Names)),ROWS(C$2:C3))))

Put this in B4

=SUMPRODUCT(--(Names=B1),--(Values<>876))

 

[T. Valko]

Hi Biff,

Nope, it's just that value I didn't want! I can see how you've amended the formula so i will give it a try now...

Thanks in advance!

I forgot to add...

You'd have to update the count formula in B4 to reflect the new condition.

=SUMPRODUCT(--(Names=B1),--(Values<>876))

 

[videozvideoz]

Hi Both,

Putting =SUMPRODUCT(--(Names=B1),--(Values<>876))

in B4 just returns '#NUM'!

Also, what if I wanted the 876 to be text such as 'XX8AS'?

Thanks guys for your help so far! We're getting there

 

[T. Valko]

Hi Both,

Putting =SUMPRODUCT(--(Names=B1),--(Values<>876))

in B4 just returns '#NUM'!

Also, what if I wanted the 876 to be text such as 'XX8AS'?

Thanks guys for your help so far! We're getting there

In Excel versions prior to Excel 2007 you can't use entire columns as range references in certain functions in array formulas.

Do Names and Values refer to entire columns?

 

[AlphaFrog]

Hi Both,

Putting =SUMPRODUCT(--(Names=B1),--(Values<>876))

in B4 just returns '#NUM'!

Also, what if I wanted the 876 to be text such as 'XX8AS'?

Thanks guys for your help so far! We're getting there

Just replace 876 with "XX8AS" in quotes

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

Or you could use a cell reference (A1 contains "XX8AS")
=IF(ROWS(C$2:C3)>B$4,"",INDEX(Values,SMALL(IF((Names=B$1)*(Values<>A1),ROW(Names)),ROWS(C$2:C3))))

I'm not sure why the SUMPRODUCT formula is not working. It's pretty straightforward.

 

[videozvideoz]

Hi Both,

Right, I've got SUMPRODUCT working - Biff was right, I had the ranges set up as $B:$B but i've changed them now so they have a valid range $B2:$B100 and this makes SUMPRODUCT work.

Right... the <>876 in the formula worked for missing out the 1st '876' but further down the list it didn't ignore it. Any ideas?

Thanks!

 

[videozvideoz]

Ok, I've sorted that one now! Last question, I promise

As well as the <>876, I also need to exclude a value in another cell. Can I have 2 conditions? For example <>876 and <(P20-80)...

Thanks

 

[T. Valko]

Hi Both,

Right, I've got SUMPRODUCT working - Biff was right, I had the ranges set up as $B:$B but i've changed them now so they have a valid range $B2:$B100 and this makes SUMPRODUCT work.

Right... the <>876 in the formula worked for missing out the 1st '876' but further down the list it didn't ignore it. Any ideas?

Thanks!

Since you redefined the scope of the range references you probably need to incorporate an "offset correction" in the extraction formula.

Like this:

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

 

[videozvideoz]

I actually amended it to $B1:$B100 so I don't need this bit -MIN(ROW(Names))+1

As well as the <>876, I also need to exclude a value in another cell. Can I have 2 conditions? For example <>876 and <(P20-80)...

Thanks