If ISNUMBER MATCH function not working.

**eurycea** · 05-29-2013, 03:14 PM

Hello,

I have a worksheet of 144484 species locality records for around 600 species. Column A is species name, B is latitude, and C is longitude. I have another column, D, That only contains 471 species names. I need to extract only the records, Column A through C, that correspond to Column D. I have been Trying to use this formula in column E: =IF(ISNUMBER(MATCH(A2, D2:D471,0)),"1","0").
It returns all 0 which would mean there are no matches. I know, however that there are matches because If I enter the same formul but match D2 to anything in A, =IF(ISNUMBER(MATCH(D2,A2:A144484,0)),"1","0"), it returns 1s for all of the 471 species. The only reason I am using this formula is because I am familiar with it so I am open to new suggestions. I was going to sort by column D then extract all records with a "1" in column D. I tried to attach my workbook but it is too large. I can cut it down and attach it but I don`t know how much I should cut it down. Let me know if this doesn`t make sense and I will try to explain better.

Thanks,

Blake

**ChemistB** · 05-29-2013, 03:20 PM

Hard to say. So if there is a match in A and D, it is an exact match?

In E2, you should have
=IF(ISNUMBER(MATCH(A2, $D$2:$D$471,0)),"1","0")
so the range stays constant when you copy it down.
Did that help?

**Jonmo1** · 05-29-2013, 03:21 PM

Perhaps you need to Lock the range..

=IF(ISNUMBER(MATCH(A2, D$2:D$471,0)),"1","0")

Without those $'s, the formula change from
=IF(ISNUMBER(MATCH(A2, D2:D471,0)),"1","0")
to
=IF(ISNUMBER(MATCH(A3, D3:D472,0)),"1","0")
to
=IF(ISNUMBER(MATCH(A4, D4:D473,0)),"1","0")
As you fill down, so it's not actually looking in the correct range all the way down.

**eurycea** · 05-29-2013, 03:25 PM

I need to do some more checking but it looks to have worked. Thank you so much. Been working on this for 2 days now. You are my hero.

**Jim0734** · 12-20-2018, 10:24 AM

There's another solution! If you use use the 'dragger' to count a list of numbers - for example you write 0.1 then 0.11 in the next cell and drag it down or across to count ie 0.1, 0.11, 0.12 etc for some reason it doesn't count exactly. If you look at the number it might say 0.12000000001. This means obviously that it isn't a MATCH so does not return the result expected. To resolve this - ROUND the number to the correct format eg =ROUND(A2,2) then apply the formula to the rounded number.