+ Reply to Thread
Results 1 to 5 of 5

Index and Match with OR

  1. #1
    Registered User
    Join Date
    11-12-2012
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 201-
    Posts
    4

    Smile Index and Match with OR

    Hello all. My name is Michelle and I am brand new to this forum. I just joined because found this to be a great site for finding answers to questions so here goes my 1st question. I am trying to match values in one column to values from multiple tables and it is not working. Here is what works when looking for a match from one list: =INDEX(BG:BG,MATCH(BM13,BC:BC),0). I find the match in column BC and it returns the value in column BG. My problem: I also want to look in column AW and return the value in column BA if the match is found there instead. I have used the or statement but get an error. Here is the syntax: =INDEX (OR(BG:BG,MATCH(BM13,BC:BC),0),(BA:BA,match(BM13,AW:AW),0)) Can someone help me? Thank you in advance.
    Last edited by Michelle Mitchell; 11-12-2012 at 06:18 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Index and Match with OR

    requests deleted
    Last edited by protonLeah; 11-13-2012 at 02:38 AM.
    Ben Van Johnson

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Index and Match with OR

    Do you mean that you want to check BC first, and if there is a match then return BG, but if not then search AW and return BA if there is a match? And return zero if no match is found? If so, try this:

    =iferror(INDEX(BG:BG,MATCH(BM13,BC:BC),iferror(INDEX(BA:BA,MATCH(BM13,AW:AW),0))

    Hope this helps.

    Pete

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Index and Match with OR

    =INDEX (OR(BG:BG,MATCH(BM13,BC:BC),0),(BA:BA,match(BM13,AW:AW),0))
    Maybe
    =IF(ISERROR(INDEX(BG:BG,MATCH(BM13,BC:BC,0)),INDEX(BA:BA, MATCH(BM13,AW:AW,0)),INDEX(BG:BG,MATCH(BM13,BC:BC,0))

    or, if 2011 allows it
    =IFERROR(INDEX(BG:BG, MATCH(BM13, BC:BC,0)), INDEX(BA:BA, MATCH(BM13, AW:AW, 0)))
    Did that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    11-12-2012
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 201-
    Posts
    4

    Re: Index and Match with OR

    Thanks ChemistB. This was a perfect solution

+ 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