Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: What is wrong with this index/match function?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =INDEX('sheet2'!$A$2:$H$189,MATCH(B15,'sheet2'!$A$2:$A$189,FALSE),1)

    I know the value of sheet 1 b15 is in sheet 2 column A. It keeps giving me an NA# answer. I am obviously missing something small.

    Thanks!

    [ This Message was edited by: EdE on 2002-04-25 10:12 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Make sure there are no trailing spacing in your source data or your lookup table. If there are, use the Trim function to clean it up.
    It's never too late to learn something new.

    Ricky

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That was the first thing I thought of. I have used left/right justify to make sure I got rid of all of them. I have even cut the range to all numbers same length and still have the problem. Any more ideas?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-25 10:10, EdE wrote:
    =INDEX('sheet2'!$A$2:$H$189,MATCH(B15,'sheet2'!$A$2:$A$189,FALSE),1)

    I know the value of sheet 1 b15 is in sheet 2 column A. It keeps giving me an NA# answer. I am obviously missing something small.

    Thanks!

    [ This Message was edited by: EdE on 2002-04-25 10:12 ]
    What are you trying to return from which column of 'sheet2'!$A$2:$H$189?

    The formula as it stands should return a value (which is identical to the value in B15). If that is what you indend to do, then

    =IF(ISNUMBER(MATCH(B15,'sheet2'!$A$2:$A$189,FALSE)),B15,"Not Found")

    would suffice. If the value of B15 is in 'sheet2'!$A$2:$A$189 and you get #N/A, then they are in fact different--differently formatted or differently typed like with extraneous spaces.

    Aladin

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you're working with numbers, could it be one is text and the other is a value. Alsi, try copying the contents of B15, sheet 1, to any location in the match range on sheet2 to see if it finds it.
    It's never too late to learn something new.

    Ricky

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    Try if this works:

    =MATCH(B15&"",'sheet2'!$A$2:$A$189,0)

    If so, the underlying format of A2:A189 must be text.

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ding ding ding we have a winner. Dont I feel stupid. Now, how do I get back formatted as a number? I did format cells, number, and no change. I can start as number, then change to text, and then back, but cant starting with text. I can click the individual cell, then enter and it changes, but I dont want to do 2000 cells individually.

    Thanks!!

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-25 10:42, EdE wrote:
    Ding ding ding we have a winner. Dont I feel stupid. Now, how do I get back formatted as a number? I did format cells, number, and no change. I can start as number, then change to text, and then back, but cant starting with text. I can click the individual cell, then enter and it changes, but I dont want to do 2000 cells individually.

    Thanks!!
    Copy an unused empty cell, select the problem range, activate Edit|Paste Special >Add.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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