Iferror Formula Update

[JRAMZ88]

Hey guys, hope all of you are doing well!

Im working on an excel formula and I'm looking to tweak it.. right now, the formula below brings in the match.. how do I tweak the formula so if there is a match, to put a "1" in the cell?

=+IFERROR(VLOOKUP((CONCATENATE(A3," - ",B3)),'P1'!A:A,1,0),"0")


Please let me know if this is enough information.. or if I need to provide additional context.


Thank you!

 

[PeteWright]

This Formula returns only 1 or 0 depending if the value was found:

=IF(ISNA(MATCH(A3&" - "&B3,'P1'!A:A,0)),0,1)

 

[JRAMZ88]

Thanks Pete! that works perfectly.

As I think this thru, how would we update the formula if there's a match enter "1" and turn the cell green?
If no match and returns "0", turn the cell red?

Can that be done within the formula, or is that more of a conditional format?

 

[PeteWright]

Thanks Pete! that works perfectly.

As I think this thru, how would we update the formula if there's a match enter "1" and turn the cell green?
If no match and returns "0", turn the cell red?

Can that be done within the formula, or is that more of a conditional format?

That's Conditional Formatting.

A formula can only change the value of the cell it is in.

 

[Scott Huish]

You could shorten that a bit if you looked at it from the point of if the value was found vs. if it wasn't.

=--ISNUMBER(MATCH(A3&" - "&B3,'P1'!A:A,0))