INDEX MATCH with Final Value Return Change
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: INDEX MATCH with Final Value Return Change
Thanks Thanks: 0 Likes Likes: 0

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

    Default INDEX MATCH with Final Value Return Change

    Hello all,

    If Array/Formula below returns a 2, change return to Released. Is it possible to integrate into formula below?

    Code:
    =IFERROR(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),"")

    Regards,

    Morey

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,662
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INDEX MATCH with Final Value Return Change

    @Morey,

    It might be beneficial if you could limit the rows rather than use full columns but maybe like....

    =IF(FERROR(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),"")=2 ,"Released",
    IFERROR(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),""))

    Hope that helps.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

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

    Default Re: INDEX MATCH with Final Value Return Change

    Snakehips,

    That definitely works on my end. Since, we are at it. How do I add an additional value change? For instance, 2 = Released and 3 = Pending.

    Thank you,

    Morey

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: INDEX MATCH with Final Value Return Change

    Will your values always be numeric?
    Also what should happen if the return is 1?
    - 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
    Jun 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH with Final Value Return Change

    If the formula just returns small integers;

    =IFERROR(CHOOSE(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),[what to return for 1], [what to return for 2], [what to return for 3] etc),"")

  6. #6
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,662
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INDEX MATCH with Final Value Return Change

    Maybe try...

    =IFERROR(CHOOSE((INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0))),"","Released","Pending"),"")

    Confirm with Ctrl + Shift + Enter
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

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

    Default Re: INDEX MATCH with Final Value Return Change

    Fluff,

    Quote Originally Posted by Fluff View Post
    Will your values always be numeric?
    Also what should happen if the return is 1?
    Oddly, not all values are numeric. D = In Review...

    Morey

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

    Default Re: INDEX MATCH with Final Value Return Change

    Snakehips,

    Quote Originally Posted by RossTattersall View Post
    If the formula just returns small integers;

    =IFERROR(CHOOSE(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),[what to return for 1], [what to return for 2], [what to return for 3] etc),"")
    Quote Originally Posted by RossTattersall View Post
    If the formula just returns small integers;

    =IFERROR(CHOOSE(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),[what to return for 1], [what to return for 2], [what to return for 3] etc),"")
    The issue I'm running into is that one of the multiple values is alpha numeric (D = In Review). How can Alpha & Numeric both be accounted for under the same formula? Possible?

    Morey
    Last edited by Morey; Jun 19th, 2019 at 01:40 PM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: INDEX MATCH with Final Value Return Change

    You could create a lookup table for the 2,3,D etc & then use that to return the text you want.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH with Final Value Return Change

    I agree with Fluff in that case. Add a sheet with a table of what outcomes should lead to what outcomes and do something like;

    IFERROR(VLOOKUP(INDEX(…),[table],2,0),"")

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
  •