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

 

[T. Valko]

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

Ok...

Does this mean: <(P20-80)... less than cell P20 minus 80 ?


If so, use a couple of cells to hold those criteria:

• X1 = 876
• X2 = =P20-80

Then:

Formula in B4:

=SUMPRODUCT(--(Names=B1),--(ISNA(MATCH(Values,X1:X2,0))))

Extraction formula:

=IF(ROWS(C$2:C3)>B$4,"",INDEX(Values,SMALL(IF(Names=B$1,IF(ISNA(MATCH(Values,X$1:X$2,0)),ROW(Names))),ROWS(C$2:C3))))

 

[videozvideoz]

Hehe getting complicated! Is that the only way to do it by holding the values in other cells? I'll give your suggestion a go in the meantime. Yep, you had it correct less than P20-80. Thanks for all your help

 

[T. Valko]

Hehe getting complicated! Is that the only way to do it by holding the values in other cells? I'll give your suggestion a go in the meantime. Yep, you had it correct less than P20-80. Thanks for all your help

Hold on there a sec.

I'm getting a bit confused!

What are the conditions for Values?

It's: not equal to 876 *OR* is less than P20-80. Is that correct?

 

[videozvideoz]

Sorry for the delay. The condition should be:

not equal to 876 *AND* is less than P20-80

Thanks again!

 

[T. Valko]

Sorry for the delay. The condition should be:

not equal to 876 *AND* is less than P20-80

Thanks again!

Try this...

Array entered**:

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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[videozvideoz]

Try this...

Array entered**:

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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hi Biff. IV done that but I'm getting #NUM! as the result. Did i need to change the SUMPRODUCT formula?

Thanks

 

[T. Valko]

Try this...

Array entered**:


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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hi Biff. IV done that but I'm getting #NUM! as the result. Did i need to change the SUMPRODUCT formula?

Thanks

Dang! I'm having one of those days where I can't do anything right.

I forgot about what can happen when posting formulas with the < and > characters in them. As a result, half of the formula I posted is missing!

Also, yes, you are correct. We need to tweak the count formula.

Count formula in B4:

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

Extraction formula array entered**:

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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[videozvideoz]

Something funny... when I pressed 'quote' instead of 'reply' to your previous post (with partially missing formula), the whole formula was displayed and not cut off! Very strange.

Now for something even stranger.. I entered the complete formula without updating the SUMPRODUCT formula and it seemed to have worked. Should this be the case?

 

[T. Valko]

Something funny... when I pressed 'quote' instead of 'reply' to your previous post (with partially missing formula), the whole formula was displayed and not cut off! Very strange.

Now for something even stranger.. I entered the complete formula without updating the SUMPRODUCT formula and it seemed to have worked. Should this be the case?

Well, the as long as the count formula returns something >0 then the extraction formula will work but it may not return all the results.