IF Match Function

[maxthebear]

I am trying to write a formula that says if a name is in a column of names put a 1 otherwise put 0. Should be a simple If, match function but having no luck.

I have attached a simple example........

thanks to all

Max

[khamilton]

Put this in H5 =IF(G5=$E$5:$E$9,1,0) and copy down

[teylyn]

Hi, you could use

=IF(COUNTIF(E5:E9,G5),1,0)

If you use match instead of countif, you will have to cater for non-found values and wrap match into another formula, something like

=IF(ISNA(MATCH(G5,E5:E9,0)),0,1)

hth

[teylyn]

khamilton, not quite.

In the example file put "Nancy" in E9. Now H6 should return a 1 but it does not.

Run your formula through the Evaluate Formula tool and see that only the value of the same row is compared, but not the values of the complete range.

[JBeaucaire]

Put this in H5 =IF(G5=$E$5:$E$9,1,0) and copy down

That only seems to work because the OP's matches are on the same rows in the example data, your formula isn't actually an array, it is just matching the current row.

You need this in H5 and then copied down:

=ISNUMBER(MATCH(G5, E:E, 0))

This will result in TRUE/FALSE answers. If you really need 1s and 0s:

=IF(ISNUMBER(MATCH(G5, E:E, 0)), 1, 0)

[khamilton]

Thank you both!!!!