+ Reply to Thread
Results 1 to 5 of 5

Index and Match Functions

  1. #1
    Damien
    Guest

    Index and Match Functions

    Good Morning All,

    I have read some peoples suggestions as to how to lookup a value based on
    two or more criteria using a combination of INDEX and MATCH fuctions. I have
    tried to repicate this myself however my fuctions always return a #NA error.
    Below is the data and function as it appears normally and under that is the
    same range, showing the formula. I am try to match a model and a serial and
    return the result (WO or R). I can't see why this formula isn't working, as
    far as I've seen its should work on a combination of letters and numbers. If
    anyone can see how to correct this could you please let me know.

    Unit Serial Result Find Unit Find Serial Produce Result
    AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
    AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
    AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A

    Unit Serial Result Find Unit Find Serial Produce Result
    AOT24RZAL T006183 WO AOT24RZAL T007509
    =INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
    AOT24RZAL T005294 R AOT24RZAL T006183
    =INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
    AOT24RZAL T007509 WO AOT24RZAL
    T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))



  2. #2
    Bob Phillips
    Guest

    Re: Index and Match Functions

    you need to array enter it, it should be committed with Ctrl-Shift-Enter,
    not just Enter.

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Damien" <Damien@discussions.microsoft.com> wrote in message
    news:612D69FB-2234-4500-AC38-D2D938B85D00@microsoft.com...
    > Good Morning All,
    >
    > I have read some peoples suggestions as to how to lookup a value based on
    > two or more criteria using a combination of INDEX and MATCH fuctions. I

    have
    > tried to repicate this myself however my fuctions always return a #NA

    error.
    > Below is the data and function as it appears normally and under that is

    the
    > same range, showing the formula. I am try to match a model and a serial

    and
    > return the result (WO or R). I can't see why this formula isn't working,

    as
    > far as I've seen its should work on a combination of letters and numbers.

    If
    > anyone can see how to correct this could you please let me know.
    >
    > Unit Serial Result Find Unit Find Serial Produce Result
    > AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
    > AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
    > AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A
    >
    > Unit Serial Result Find Unit Find Serial Produce Result
    > AOT24RZAL T006183 WO AOT24RZAL T007509
    > =INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
    > AOT24RZAL T005294 R AOT24RZAL T006183
    > =INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
    > AOT24RZAL T007509 WO AOT24RZAL
    > T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))
    >
    >




  3. #3
    Damien
    Guest

    Re: Index and Match Functions

    Sorry I forgort to mention, I've already done that, it still gives the same
    result. Any other ideas?

    "Bob Phillips" wrote:

    > you need to array enter it, it should be committed with Ctrl-Shift-Enter,
    > not just Enter.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "Damien" <Damien@discussions.microsoft.com> wrote in message
    > news:612D69FB-2234-4500-AC38-D2D938B85D00@microsoft.com...
    > > Good Morning All,
    > >
    > > I have read some peoples suggestions as to how to lookup a value based on
    > > two or more criteria using a combination of INDEX and MATCH fuctions. I

    > have
    > > tried to repicate this myself however my fuctions always return a #NA

    > error.
    > > Below is the data and function as it appears normally and under that is

    > the
    > > same range, showing the formula. I am try to match a model and a serial

    > and
    > > return the result (WO or R). I can't see why this formula isn't working,

    > as
    > > far as I've seen its should work on a combination of letters and numbers.

    > If
    > > anyone can see how to correct this could you please let me know.
    > >
    > > Unit Serial Result Find Unit Find Serial Produce Result
    > > AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
    > > AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
    > > AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A
    > >
    > > Unit Serial Result Find Unit Find Serial Produce Result
    > > AOT24RZAL T006183 WO AOT24RZAL T007509
    > > =INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
    > > AOT24RZAL T005294 R AOT24RZAL T006183
    > > =INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
    > > AOT24RZAL T007509 WO AOT24RZAL
    > > T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))
    > >
    > >

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: Index and Match Functions

    No ideas I am afraid. The absolute version

    =INDEX($C$29:$C$31,MATCH(D29&E29,$A$29:$A$31&$B$29:$B$31,0))

    works fine for me.

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Damien" <Damien@discussions.microsoft.com> wrote in message
    news:18D5B0C7-2F2F-46FF-B102-248CE50284E2@microsoft.com...
    > Sorry I forgort to mention, I've already done that, it still gives the

    same
    > result. Any other ideas?
    >
    > "Bob Phillips" wrote:
    >
    > > you need to array enter it, it should be committed with

    Ctrl-Shift-Enter,
    > > not just Enter.
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (replace somewhere in email address with gmail if mailing direct)
    > >
    > > "Damien" <Damien@discussions.microsoft.com> wrote in message
    > > news:612D69FB-2234-4500-AC38-D2D938B85D00@microsoft.com...
    > > > Good Morning All,
    > > >
    > > > I have read some peoples suggestions as to how to lookup a value based

    on
    > > > two or more criteria using a combination of INDEX and MATCH fuctions.

    I
    > > have
    > > > tried to repicate this myself however my fuctions always return a #NA

    > > error.
    > > > Below is the data and function as it appears normally and under that

    is
    > > the
    > > > same range, showing the formula. I am try to match a model and a

    serial
    > > and
    > > > return the result (WO or R). I can't see why this formula isn't

    working,
    > > as
    > > > far as I've seen its should work on a combination of letters and

    numbers.
    > > If
    > > > anyone can see how to correct this could you please let me know.
    > > >
    > > > Unit Serial Result Find Unit Find Serial Produce Result
    > > > AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
    > > > AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
    > > > AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A
    > > >
    > > > Unit Serial Result Find Unit Find Serial Produce

    Result
    > > > AOT24RZAL T006183 WO AOT24RZAL T007509
    > > > =INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
    > > > AOT24RZAL T005294 R AOT24RZAL T006183
    > > > =INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
    > > > AOT24RZAL T007509 WO AOT24RZAL
    > > > T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))
    > > >
    > > >

    > >
    > >
    > >




  5. #5
    RagDyeR
    Guest

    Re: Index and Match Functions

    99% of the time, the reason that suggested solutions (formulas) don't work
    for the OPs, and *do* work for the responders, is that the responders test
    their suggestions with their own keyed in data which is an *exact* match
    between the datalist and the lookup list, whereas the OPs data is a return
    of a formula or an import from another data source.

    This returned or imported data most often turns out to *not* be what the OP
    expected, and therefore did not accurately depict in their (OP) lookup list
    (formula).

    There's almost always imbedded, invisible characters (spaces - web
    characters) that contaminate the datalist, making exact matches
    unachievable.

    One true test of the veracity of the formula *itself*, is to manually key in
    test data in the datalist and the lookup list to insure that there is an
    exact match between them both.

    If that makes the formula perform as expected, then the data must be
    examined for contamination.
    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "Damien" <Damien@discussions.microsoft.com> wrote in message
    news:18D5B0C7-2F2F-46FF-B102-248CE50284E2@microsoft.com...
    Sorry I forgort to mention, I've already done that, it still gives the same
    result. Any other ideas?

    "Bob Phillips" wrote:

    > you need to array enter it, it should be committed with Ctrl-Shift-Enter,
    > not just Enter.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "Damien" <Damien@discussions.microsoft.com> wrote in message
    > news:612D69FB-2234-4500-AC38-D2D938B85D00@microsoft.com...
    > > Good Morning All,
    > >
    > > I have read some peoples suggestions as to how to lookup a value based

    on
    > > two or more criteria using a combination of INDEX and MATCH fuctions. I

    > have
    > > tried to repicate this myself however my fuctions always return a #NA

    > error.
    > > Below is the data and function as it appears normally and under that is

    > the
    > > same range, showing the formula. I am try to match a model and a serial

    > and
    > > return the result (WO or R). I can't see why this formula isn't

    working,
    > as
    > > far as I've seen its should work on a combination of letters and

    numbers.
    > If
    > > anyone can see how to correct this could you please let me know.
    > >
    > > Unit Serial Result Find Unit Find Serial Produce Result
    > > AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
    > > AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
    > > AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A
    > >
    > > Unit Serial Result Find Unit Find Serial Produce Result
    > > AOT24RZAL T006183 WO AOT24RZAL T007509
    > > =INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
    > > AOT24RZAL T005294 R AOT24RZAL T006183
    > > =INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
    > > AOT24RZAL T007509 WO AOT24RZAL
    > > T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))
    > >
    > >

    >
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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