Results 1 to 9 of 9

Thread: LARGE Formula & Return value from MATCH
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2005
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default LARGE Formula & Return value from MATCH

    I have the below Formula using LARGE and right now it returns the largest value in the column, but the Column is a UID field and I am just using it to determine unique value, I want to somehow wrap this formula with and INDEX / MATCH array and first use LARGE to find the largest UID, then using Index / match to match that UID to another field and return the value from that field

    =LARGE(IF('2H Pipeline'!E:E=U2,IF('2H Pipeline'!B:B=G5,'2H Pipeline'!C:C)),G4)

  2. #2
    Board Regular
    Join Date
    Jan 2005
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LARGE Formula & Return value from MATCH

    anyone have any ideas on this

  3. #3
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: LARGE Formula & Return value from MATCH

    something like this?

    ABCDE
    1UIDUID
    211V1
    322V2
    433V3
    544V4
    655V5
    7106V6
    897V7
    988V8
    1079V9
    11610V10
    12
    13
    14V10

    Sheet1



    Worksheet Formulas
    CellFormula
    A14=INDEX(E2:E11,MATCH(LARGE(A2:A11,1),D2:D11,0))


  4. #4
    Board Regular
    Join Date
    Jan 2005
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LARGE Formula & Return value from MATCH

    Thank you

    to do this do I need to duplicate my UID column and have a column A version and a Column D version ?

  5. #5
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: LARGE Formula & Return value from MATCH

    may be easier if you post a small sample of the data and desired result here.

  6. #6
    Board Regular
    Join Date
    Jan 2005
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LARGE Formula & Return value from MATCH

    thank you for your quick reply. I think I follow what your formula is doing and I sorta got it to work but it would only return #NA , I figured that was possibly because it did not have the conditional IF statements in it as well. so I added the IF statements but I get syntax error with it

    {=INDEX('2H Pipeline'!A2:R150,MATCH(LARGE(IF('2H Pipeline'!E:E,=U2,IF('2H Pipeline'!B:B=L5,('2H Pipeline'!R:R,1),'2H Pipeline'!C:C,)))),L4-1)}

    the goal is
    index the entire pipeline sheet
    if the user = U2 (column E from the pipeline sheet)
    if the status =L5 (column B from the pipeline sheet)
    find the largest value in the UID list (column R)
    return the value in the TCV column from that same row (column C)
    the L4-1 is there to parse through each record that meet the given criteria (the is a COUNTIF formula in L4 that is doing a sum of all records that meet the user / status criteria

    here is a sample of the formula that works using only LARGE, the issue with LARGE is it is using the TCV field and that is not always unique

    =IFERROR(LARGE(IF('2H Pipeline'!E:E=U2,IF('2H Pipeline'!B:B=L5,'2H Pipeline'!C:C)),L4-1),"")

    I am not sure how to post the table like you have to show you a sample of the data from the pipeline sheet but here it is in txt format

    Customer = Column A
    Odds (%) = Column B
    TCV = Column C
    Forecast Date =Column D
    Owner =Column E
    Digital / CSE =Column F
    Description = Column G
    Type =Column H
    SC Number =Column I
    Opportunity Tower =Column J
    Owner Column =Column K
    Service Contract # Column L
    Start Date Column =Column M
    End Date =Column N
    Active =Column O
    Link =Column P
    SC # =Column Q
    UID =Column R
    Customer name Stretch $200,000.00 19-Aug-19 User name User assist Deal detail Channel SC Link Tower Market Segment Number Start End Yes Link F4-YD94FLR# 202,170.00

  7. #7
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: LARGE Formula & Return value from MATCH

    see if this is what you're after, btw I've reduced the ranges for testing but try not to reference the whole column as if will slow thing down.

    Ex1

    ABCDEFR
    1Customer Odds (%) TCV Forecast Date Owner Digital / CSE UID
    21A360B1035
    32A910B1044
    43A610B1031
    54A830B1037
    65A160B1063
    76A590B1021
    87A640B1052
    98A810B1016
    109A880B1048

    2H Pipeline





    ALTU
    1
    2B
    3
    41602
    5A

    Sheet2



    Array Formulas
    CellFormula
    A4{=IFERROR(INDEX('2H Pipeline'!C2:C11,MATCH(LARGE(IF('2H Pipeline'!E2:E11=U2,IF('2H Pipeline'!B2:B11=L5,'2H Pipeline'!R2:R11)),L4-1),'2H Pipeline'!R2:R10,0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Ex 2

    ABCDEFR
    1Customer Odds (%) TCV Forecast Date Owner Digital / CSE UID
    21A360B1035
    32A910B1044
    43A6101031
    54A8301037
    65A1601063
    76A5901021
    87A6401052
    98A810B1016
    109A880B1048

    2H Pipeline





    ALTU
    1
    2B
    3
    48802
    5A

    Sheet2



    Array Formulas
    CellFormula
    A4{=IFERROR(INDEX('2H Pipeline'!C2:C11,MATCH(LARGE(IF('2H Pipeline'!E2:E11=U2,IF('2H Pipeline'!B2:B11=L5,'2H Pipeline'!R2:R11)),L4-1),'2H Pipeline'!R2:R10,0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


  8. #8
    Board Regular
    Join Date
    Jan 2005
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LARGE Formula & Return value from MATCH

    Thank you I tried both examples and both work, however I tested changing multiple lines having the same TCV and it still has the same issue I had when using only "Large" based on the TCV column.

    I originally added the UID column to first find the largest UID that meet the criteria. Then return the TCV value from that row. Your example seems to find the largest UID but is returning the first match on TCV, and I can not figure out why because I made two TCV's identical with different users and a user that did not even match the IF criteria

    Quote Originally Posted by AlanY View Post
    see if this is what you're after, btw I've reduced the ranges for testing but try not to reference the whole column as if will slow thing down.

    Ex1

    ABCDEFR
    1Customer Odds (%) TCV Forecast Date Owner Digital / CSE UID
    21A360B1035
    32A910B1044
    43A610B1031
    54A830B1037
    65A160B1063
    76A590B1021
    87A640B1052
    98A810B1016
    109A880B1048

    2H Pipeline





    ALTU
    1
    2B
    3
    41602
    5A

    Sheet2



    Array Formulas
    CellFormula
    A4{=IFERROR(INDEX('2H Pipeline'!C2:C11,MATCH(LARGE(IF('2H Pipeline'!E2:E11=U2,IF('2H Pipeline'!B2:B11=L5,'2H Pipeline'!R2:R11)),L4-1),'2H Pipeline'!R2:R10,0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Ex 2

    ABCDEFR
    1Customer Odds (%) TCV Forecast Date Owner Digital / CSE UID
    21A360B1035
    32A910B1044
    43A6101031
    54A8301037
    65A1601063
    76A5901021
    87A6401052
    98A810B1016
    109A880B1048

    2H Pipeline





    ALTU
    1
    2B
    3
    48802
    5A

    Sheet2



    Array Formulas
    CellFormula
    A4{=IFERROR(INDEX('2H Pipeline'!C2:C11,MATCH(LARGE(IF('2H Pipeline'!E2:E11=U2,IF('2H Pipeline'!B2:B11=L5,'2H Pipeline'!R2:R11)),L4-1),'2H Pipeline'!R2:R10,0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

  9. #9
    Board Regular
    Join Date
    Jan 2005
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LARGE Formula & Return value from MATCH

    I think I may know what the issue is, testing another change, stand by

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
  •