Results 1 to 7 of 7

Thread: Index Match with result coming from 1 of 2 Columns

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index Match with result coming from 1 of 2 Columns

    Hello.. I am trying to get a formula so that when A1 matches a Product ID in Sheet2,Column B, it will
    a) return the data from the relevant row in Column D, if the corresponding row in column Q = No or,
    b) return the data from the relevant row in Column R, if the corresponding row in column Q = Yes

    I've tried lots of variations like these two but none are working so I'm thinking I must be on the wrong track.
    =IF('sheet2'!Q:Q ="No", INDEX('sheet2'!D:D,MATCH(A1,'sheet2'!B:B,0)),INDEX('sheet2'!R:R,MATCH(A1,'sheet2'!B:B,0)))

    =IFERROR(INDEX('sheet2'!D:D,MATCH(A1,'sheet2'!B:B,0),MATCH('yesnosheet'!A1,'sheet2'!Q:Q,0)),INDEX('sheet2'!R:R,MATCH(A1, 'sheet2'!B:B,0),MATCH('yesnosheet'!A2,'sheet2'!Q:Q, 0)))

  2. #2
    Board Regular jimrward's Avatar
    Join Date
    Feb 2003
    Location
    Kingdom of Fife
    Posts
    1,689
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match with result coming from 1 of 2 Columns

    Can column Q be anything else or will it always be yes or no

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match with result coming from 1 of 2 Columns

    Hi .. it will always be Yes or No

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,362
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Index Match with result coming from 1 of 2 Columns

    How about
    =IF(INDEX(Sheet2!Q:Q,MATCH(A1,Sheet2!B:B,0))="No", INDEX(Sheet2!D:D,MATCH(A1, Sheet2!B:B,0)), INDEX(Sheet2!R:R,MATCH(A1, Sheet2!B:B,0)))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match with result coming from 1 of 2 Columns

    WOW! that's working perfectly thank you so much.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,362
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Index Match with result coming from 1 of 2 Columns

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,005
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Index Match with result coming from 1 of 2 Columns

    What about just
    =VLOOKUP(A1,Sheet2!B:R,3+14*(INDEX(Sheet2!Q:Q,MATCH(A1,Sheet2!B:B,0))="Yes"),0)

    Or if you want to stick to all index/match
    =INDEX(Sheet2!D:R,MATCH(A1,Sheet2!B:B,0),1+14*(INDEX(Sheet2!Q:Q,MATCH(A1,Sheet2!B:B,0))="Yes"))
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •