If function to match colunms

[Haz]

Hi,

I have a column of numbers in A which I would like to match if they appear
in col G, I am using the following if function this works for only 1 cell and
when I drag this down it returns no match when I know there is a match
available.

IF(A2:A15=G:G,"Match","no Match")

Any help would be appreciated

 

[Pete_UK]

Try it this way:

=IF(ISNA(MATCH(A2,G:G,0)),"no match","match")

It check to see if there is a match between A2 and anything in column
G. Put this on row 2 and copy down for as many entries as you have in
column A (15?) to check for the other numbers.

Hope this helps.

Pete

 

[Bob Phillips]

=IF(SUMPRODUCT(--(COUNTIF(A2:A15,G1:G100)))>0,"Match","No match")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Haz]

Thank You that work perfectly.

Try it this way:

=IF(ISNA(MATCH(A2,G:G,0)),"no match","match")

It check to see if there is a match between A2 and anything in column
G. Put this on row 2 and copy down for as many entries as you have in
column A (15?) to check for the other numbers.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[Haz]

Hi Pete,

Is it possible to use this function, to then return the value of the
matching cells in Col I. For example if there is a direct match for A2 in Col
E, then in either this cell of another return the value in col I relating to
Col E?

IF(ISNA(MATCH(A2,E:E,0)),"no match","match")

 

[Pete_UK]

You have a reply elsewhere which should get you the first match. If
you have multiple matches, however, and want to display a list of all
the matches then you will have to do it in a different way - which is
it to be?

Pete