If function to match colunms

  • Thread starter Thread starter Haz
  • Start date Start date
H

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
 
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
 
=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)
 
Thank You that work perfectly.

Pete_UK said:
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
Click to expand...
 
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")
 
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
 
Back
Top