+ Reply to Thread
Results 1 to 12 of 12

Stuck on Match function with #N/A; attempting to reverse Index/Match

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    Philadelphia
    MS-Off Ver
    2010
    Posts
    18

    Stuck on Match function with #N/A; attempting to reverse Index/Match

    I am quite good at excel for no training, however, have found myself to hit a wall. I have a 2 way table. Essentially what I am attempting to do is a Index/Match function but in reverse. Where as the Index/match function will look up in two differing directions of arrays and spit out the value of the intersecting point I want to do this in reverse. Example

    I have a table where I will search for the maximum values utilizing the =Match(array) function.

    Based on the number it finds within the chart/array, i need to find the corresponding row and column identifiers.


    For you visual people:

    ......A.. B. C.. D.. E
    ......a.. b.. c.. d.. e
    A a 10 14 34 42 30
    B b 22 31 52 58 10
    C c 11 15 13 14 15
    D d 16 45 18 19 20
    E e 21 22 23 24 25

    So in the above example i would use MAX function to return the value of '52'. I then need some way to spit out and identify '52' would be found in row 'C', and 'c' and column 'B' and 'b'. Please note the '.'s are just for spacing purposes. Yes there are two identifying columns/rows for the data set.

    I have attempted utilizing Match/Max function to at least identify 2 of the columns and it returns an #N/A no matter what i seem to adjust, =MATCH(MAX(C3:G7),A3:A8&B3:B8,0) where this would identify the first two column numbers in which the max value of 52 is found, but it simply will not work. Under formula evaluation it simply returns a #N/A. All values in the data table are formatted as a number with '0' decimal places. I have attempted this as well, still #N/A, =MATCH(ROUND(MAX(C3:G7),0),A3:A8&B3:B8,0)

    If I need to clarify anything further, please let me know.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    The problem with that is the way match works on the array of data.

    If you know its only going to be one value (max) then you can use sumproduct and row() or column() to do it.

    I have several examples however as I am on a noble phone I cant upload anything atm
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  3. #3
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    Lets call your data range "data"

    =sumproduct(--(data=max(data))*((column(data)-min(column(data))+1)))

    Will give you the column of your max value. When combined with index you can use that to tell you the location

    Sorry its a bit unclear

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,311

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    I would do this with a helper row

    A
    B
    C
    D
    E
    F
    G
    1
    a b c d e 4 c
    2
    1
    10
    14
    34
    42
    30
    3
    2
    22
    31
    52
    58
    10
    4
    3
    11
    15
    13
    14
    15
    5
    4
    16
    45
    18
    19
    20
    6
    5
    21
    22
    23
    24
    25
    7
    22
    45
    52
    58
    30


    B7=MAX(B2:B6) copied across
    G1=INDEX($A$2:$A$6,MATCH(MAX($B$7:$F$7),$B$7:$F$7,0))&" "&INDEX(A1:F1,MATCH(MAX($B$7:$F$7),$B$7:$F$7,0))

    @twiggy, based on the above, your suggestion, gives gives 8?
    =SUMPRODUCT(--($B$2:$F$7=MAX($B$2:$F$7))*((COLUMN($B$2:$F$7)-MIN(COLUMN($B$2:$F$7))+1)))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    Probably a typo somewhere like I said I on a phone it should read

    =SUMPRODUCT(--(B2:F6=MAX(B2:F6))*((COLUMN(B2:F6)-MIN(COLUMN(B2:F6)))+1))

    which should give 4 do same with row and then index and combine

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    Yeah I think there is a bracket in the wrong place on original post after min should be )))+1)) not ))+1))) lol

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    _____________________
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-10-2014
    Location
    Philadelphia
    MS-Off Ver
    2010
    Posts
    18

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    Quote Originally Posted by twiggywales View Post
    Lets call your data range "data"

    =sumproduct(--(data=max(data))*((column(data)-min(column(data))+1)))

    Will give you the column of your max value. When combined with index you can use that to tell you the location

    Sorry its a bit unclear

    Thank you twiggy, your equation does work out to return the correct column number. I don't fully understand its function and how it works.

    Quote Originally Posted by FDibbins View Post
    I would do this with a helper row

    A
    B
    C
    D
    E
    F
    G
    1
    a b c d e 4 c
    2
    1
    10
    14
    34
    42
    30
    3
    2
    22
    31
    52
    58
    10
    4
    3
    11
    15
    13
    14
    15
    5
    4
    16
    45
    18
    19
    20
    6
    5
    21
    22
    23
    24
    25
    7
    22
    45
    52
    58
    30


    B7=MAX(B2:B6) copied across
    G1=INDEX($A$2:$A$6,MATCH(MAX($B$7:$F$7),$B$7:$F$7,0))&" "&INDEX(A1:F1,MATCH(MAX($B$7:$F$7),$B$7:$F$7,0))

    @twiggy, based on the above, your suggestion, gives gives 8?
    =SUMPRODUCT(--($B$2:$F$7=MAX($B$2:$F$7))*((COLUMN($B$2:$F$7)-MIN(COLUMN($B$2:$F$7))+1)))
    Thank you FDibbins, your equation and helper row works well and got me where I needed to get. Much obliged.

  9. #9
    Registered User
    Join Date
    12-10-2014
    Location
    Philadelphia
    MS-Off Ver
    2010
    Posts
    18

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    Thank you for your effort. I've already utilized FDibbins equation however will review this one and learn.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,311

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

  11. #11
    Registered User
    Join Date
    12-10-2014
    Location
    Philadelphia
    MS-Off Ver
    2010
    Posts
    18

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    Done and Done. Automatically overlooked those points thinking it was a signature.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,311

    Re: Stuck on Match function with #N/A; attempting to reverse Index/Match

    Thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Resolved >>> Reverse Lookup (Index & Match)
    By SamuelT in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-21-2014, 05:53 AM
  2. Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  3. Reverse Index and Match functions
    By tsanodze in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2013, 04:06 AM
  4. Attempting to use an Averageif and index/match but having trouble
    By jmuduke08 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-11-2013, 12:15 PM
  5. reverse index match, offset problem
    By reggie1000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-12-2008, 12:18 PM

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