Results 1 to 6 of 6
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts

    MATCH function (XL2K)

    I thought I knew the MATCH function inside out.

    In my sample file attached, I'm not getting my expected results.
    If I copy my test value for matching and paste it into the [Ctrl]-F Find to find it in column [A} of my 2 data sheets, it will happily show the match locations.
    But the formulas don't.

    What have I forgotten?????

    zeddy

  2. #2
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH function (XL2K)

    Zeddy,

    You problem is that you are trying to match a number to a text field. If you will put a

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: MATCH function (XL2K)

    M,
    The strange thing is that in the original data the item I was having a problem with the match had (allegedly) a cell format of Text. So I thought I was comparing apples with apples.
    The sample I attached had 3 versions with the item formatted as Text, General and Number,
    but none of them returned the match row.
    I did precede the item with a null to get it to work, e.g. =MATCH(""&b7,...
    but it still puzzles me.
    Also, if you look on sheet [data1] of the sample workbook, cell entry [a8], this cell is allegedly of General format but displays 2 leading zeros. If you press [F2]-Edit and then [Enter] it will then lose the leading zeros and then, I assume, properly become General format.

    I have no control over the original format - the real work uses filtering to import data from some external files.
    It is important for me to get the MATCH function working irrespective of the cell formatting.

    zeddy

  4. #4
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH function (XL2K)

    Zeddy,

    It

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MATCH function (XL2K)

    Your data set is text, they are NOT numbers. The format of the cell has NO affect on the VALUE, it only change the DISPLAY. The value in the cells is text.

    You are trying to look up NUMBERS in a dataset of text so excel found no matches. adding a nullstring before looking up essentially will convert the numbers to text. To make sure they get transformed correctly you might explicitly define the format for the numbers rather than let excel use a default:
    TEXT(B7,"0") instead of B7.

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH function (XL2K)

    If you enter your match as an array formula like this:

    =MATCH(B7,1*data1!A1:A1000,FALSE)

    It works irrespective whether the list has text or numbers.
    The penalty being a slower calc of course.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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