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

=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" wrote in message
...
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

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

On Apr 29, 11:00*am, Haz wrote:
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

Thank You that work perfectly.

"Pete_UK" wrote:

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

On Apr 29, 11:00 am, Haz wrote:
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

You're welcome - thanks for feeding back.

Pete

On Apr 29, 2:05*pm, Haz wrote:
Thank You that work perfectly.

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" wrote:

You're welcome - thanks for feeding back.

Pete

On Apr 29, 2:05 pm, Haz wrote:
Thank You that work perfectly.

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

On Apr 29, 2:33*pm, Haz wrote:
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" wrote:
You're welcome - thanks for feeding back.

Pete

On Apr 29, 2:05 pm, Haz wrote:
Thank You that work perfectly.- Hide quoted text -

- Show quoted text -