Help matching one cell to another in a range

**oxyron** · 11-01-2011, 09:58 AM

Hello,

I have two worksheets that I am working with. Worksheet A and Worksheet B both have common IDs that I am trying to match against each other. My goal is for WkstA to look at the cell on that wkst (A9, for example), look to see if it is contained within a range on WkstB (B4:B90), and if there is a match just return something simple back on WkstA like "Yes" or 1 works, I dont really care.

I tried an IF statement, but had no success. Any help?

Thanks.

**NBVC** · 11-01-2011, 10:09 AM

Try

=--ISNUMBER(MATCH(A2,'WkstB'!$B$4:$B$90,0))

copied down

adjust sheetname if necessary.

this will return 1 if found, and 0 if not found.

**oxyron** · 11-01-2011, 10:16 AM

Didnt work. Maybe I should have been more clear, these IDs are words and not numbers ie. WilsonTrust or SmithRFam.

**NBVC** · 11-01-2011, 10:23 AM

It should not matter if you are checking numbers or text.. the ISNUMBER() function is checking the result of the MATCH() function, not the cell content.

Are you sure the sheetname matches exactly? Are you sure the matches are exact in both sheets? Have you double-checked the references and ranges?

**oxyron** · 11-01-2011, 10:31 AM

Heres exactly what I am using:

Please Login or Register  to view this content.

And I am 100% sure the IDs are identical. Its returning all 0's, no 1's. I even double-checked by replacing a cell on each sheet with something simple like "AA" to see if it would work.

**NBVC** · 11-01-2011, 10:32 AM

AcrFA52.xlsx is a workbook name, not a sheetname.... that is the problem

Maybe:

=--ISNUMBER(MATCH(C9,'[AcrFA52.xlsx]Sheet1'!$B$4:$B$82,0))

adjusting the sheetname to match the lookup workbook's sheet.

**oxyron** · 11-01-2011, 10:43 AM

To try and eliminate potential causes of the problem, I merged the two into one workbook, two different sheets. Sorry for the confusion. Here is where I am at now:

Please Login or Register  to view this content.

Still having issues though.

**NBVC** · 11-01-2011, 10:44 AM

What exactly is the issue? I.E. what result are you getting?

**oxyron** · 11-01-2011, 10:53 AM

Sigh. Don't worry about it, you were right. My ID's were correct, however the file I had exported from a PDF and needed to trim the cells as they had extra spaces in them. Thanks for the help NBVC, you were spot on.