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
Bookmarks