+ Reply to Thread
Results 1 to 4 of 4

ISNUMBER(MATCH)) doesn't work.

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2016
    Posts
    34

    Question ISNUMBER(MATCH)) doesn't work.

    Hello All,

    I receive a sheet that has a list of values in column J. I am the owner of a sheet which has some, but not all of those values. All of the values are numerical. What I want to do is ensure that the value in my spreadsheet is in the spreadsheet I have received.

    I used the following in Cell B2...

    IF(ISNUMBER(MATCH(A2,'[Workbook]Sheet1!'J:J,0)),A2,"Error") - We will call this formula (X)

    I know that all my numbers should be in there but it is not working. It only returns, "Error". I had a look around online and it suggested some checks should be performed. =A2=J4506 (This returned TRUE)
    I also tries ISTEXT on both the cells and they both returned TRUE even if they were values.

    Something in performing these manual steps, triggered (X) and it is now working. Any modifications I make in either sheet correctly updates the formula but it does not allow me to copy down. It goes back to recording an error it seems that when I manually link the cells, it can function. If I change A2 from 156339 to 156340, it works. If I change the other workbook from 156339 to 156340, it works. If I change A2 to A3, it doesn't.

    Can anyone help?

    L

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: ISNUMBER(MATCH)) doesn't work.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

    4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: ISNUMBER(MATCH)) doesn't work.

    Hello,

    I am fairly sure that it is something to do with the way that Excel treats numbers as text and text as numbers.

    I have been able to duplicate the problem by setting both Input01 and Input02 as cells containing text and then copying and pasting the values again.
    For Input 01, I set the cells as text and then copied into Notepad and back again. For Input 02 I set the cells to text and copied and pasted the values, *without* copying to notepad first.
    As you can see, I get Errors in the output.

    My problem is that for the real data, I am not in control of the formatting and I wonder if there is a way to force it.

    Thanks

    L
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,507

    Re: ISNUMBER(MATCH)) doesn't work.

    Perhaps as countif is not number/text sensitive

    =if(COUNTIF('[Input02.xlsx]Input 02'!$A:$A,'[Input01.xlsx]Input 01'!$A$2),'[Input01.xlsx]Input 01'!$A$2,"Error")
    or
    =if(COUNTIF('[Input02.xlsx]Input 02'!$A:$A,'[Input01.xlsx]Input 01'!$A$2),'[Input01.xlsx]Input 01'!$A$2*1,"Error") to make them all numbers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. INDEX MATCH MATCH doesn't work well in a Filtered List?
    By exceln3wb in forum Excel General
    Replies: 1
    Last Post: 10-05-2017, 06:57 AM
  2. Match Index Doesn't Work
    By benjie1984 in forum Excel General
    Replies: 3
    Last Post: 03-02-2017, 04:32 PM
  3. [SOLVED] Exact match with decimals doesn't work
    By benishiryo in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-09-2016, 12:35 AM
  4. function INDEX MATCH MATCH doesn´t work
    By leonelcd in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-31-2015, 11:36 AM
  5. Index + Match doesn't work with text
    By blueturnaround in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-19-2014, 01:18 PM
  6. Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??
    By Underling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 09:01 PM
  7. [SOLVED] Why Doesn't This MATCH Work - Array Question
    By kcc in forum Excel General
    Replies: 5
    Last Post: 12-11-2005, 03:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1