+ Reply to Thread
Results 1 to 2 of 2

Match a value, retrun correspond, if matche are more then one, retrun largest correspond

  1. #1
    Registered User
    Join Date
    11-20-2012
    Location
    PAKISTAN
    MS-Off Ver
    Excel 2007
    Posts
    1

    Match a value, retrun correspond, if matche are more then one, retrun largest correspond

    Hi , i have a sheet which contain data like this

    a b c d e f g
    1 0.014 0.55 0.27 0.9 0.05 0.31
    2 9 5 0 9 9 5
    3
    4
    5
    6
    7
    8
    9
    0

    now, if the content of a1 (1) is met into the $b$2:$g$2 then
    i would like to get the correspond value from $b$1:$g$1
    into h1 and so on till h10

    in this sheet b2, d2 & f2 contain same value but the
    correspond are not same & in this condition i would like to
    pull the largest correspond value.

    If i use this formula
    =index($b$1:$g$1,match(a1,$b$2:$g$2,0))
    it is god for unique but when table contain duplicate
    it doesn't go to 2nd or 3rd duplicate value & pulls
    correspond value of 1st duplicate value.
    Which is the smallest correspond.
    If i use this formula =index($b$1:$g$1,large(if($b$2:$g$2=a1,column($b$2:$g$2)-column($b$1),""),1))

    it gives the the largest column reference which return the
    3rd correspond value which is smaller the 2nd correspond.

    Instead of large() if i use small() then it gives the
    smallest column ref which return the 1 correspond value
    & problem remain same.

    I would like to result like that

    a b c d e f g h

    1 0.014 0.55 0.27 0.9 0.05 0.31 n/a

    2 9 5 0 9 9 5 n/a

    3 n/a

    4 0.31

    5 0.55

    6 n/a

    7 n/a

    8 n/a

    9 0.9

    0 0.27





    help will be appreciated
    thanks

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Match a value, retrun correspond, if matche are more then one, retrun largest correspo

    Hi SPIDER,

    Welcome to the forum.

    I believe you a sample file would be helpful here.. please upload one with alongwith your expected results.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

+ 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