lookup

  • Thread starter Thread starter crapit
  • Start date Start date
C

crapit

What other lookup function can be use to searches for a value in the any column of a table array
returns a value in the same row from another column in the table array
 
Err, wrong QS. I should be asking how to display all MAX values? i.e a column consists of 2 similar value 99. how to extract the next highest value?
What other lookup function can be use to searches for a value in the any column of a table array
returns a value in the same row from another column in the table array
 
Have you looked at the LARGE function? Look in Excel Help for details.

Hope this helps.

Pete
 
I tried using large LARGE(G7:G20,1), result = 60, then using
LARGE(G7:G20,2) = 60, LARGE(G7:G20,3) =55

45.00
15.00
30.00
60.00
20.00
2.00
25.00
45.00
30.00
55.00
60.00
20.00
20.00
20.00



Have you looked at the LARGE function? Look in Excel Help for details.

Hope this helps.

Pete
 
Yes, well you have two 60's in your range, so this is what you would
expect - the results are similar to applying the RANK function, and
equal values occupy two or more positions in the ranking.

What is your real question?

Pete
 
I think better to list example below. I.e Max points = 60 and it occurs
twice,
therefore I want to know the code's value. Which means

1st azxc 60.00
2nd yuui 60.00
3rd ukjk 55.00
4th


code points
abc 45.00
abcd 15.00
abce 30.00
azxc 60.00
eeew 20.00
rtyu 2.00
tyhj 25.00
ujyn 45.00
dfgr 30.00
ukjk 55.00
yuui 60.00
erwt 20.00
mnhj 20.00
nmbg 20.00



Yes, well you have two 60's in your range, so this is what you would
expect - the results are similar to applying the RANK function, and
equal values occupy two or more positions in the ranking.

What is your real question?

Pete
 
Assuming (from your earlier example) that your data is in G7:H20, and
that you want the sorted list on the same rows, then put 1 to 14 in
L7:L20 and this in N7:

=LARGE(H$7:H$20,L7)

and this can then be copied down to N20. Then you can put this formula
in M7:

=INDEX(G$7:G$20,MATCH(LARGE(H$7:H$20,L7),H$7:H$20,0))

and this one in M8:

=IF(N8=N7,INDEX(INDIRECT("G"&MATCH(M7,G$7:G$20,0)+7&":G
$20"),MATCH(N8,INDIRECT("H"&MATCH(M7,G$7:G$20,0)+7&":H$20"),0)),INDEX(G
$7:G$20,MATCH(N8,H$7:H$20,0)))

Then copy this longer formula down into M9:M20

It works by adjusting the table range to exclude any earlier codes
with the same number of points, and you should get this in L7:N20:

1 azxc 60
2 yuui 60
3 ukjk 55
4 abc 45
5 ujyn 45
6 abce 30
7 dfgr 30
8 tyhj 25
9 eeew 20
10 erwt 20
11 mnhj 20
12 nmbg 20
13 abcd 15
14 rtyu 2


Hope this helps.

Pete
 
Err quite confusing, ur H column refer to what?

Assuming (from your earlier example) that your data is in G7:H20, and
that you want the sorted list on the same rows, then put 1 to 14 in
L7:L20 and this in N7:

=LARGE(H$7:H$20,L7)

and this can then be copied down to N20. Then you can put this formula
in M7:

=INDEX(G$7:G$20,MATCH(LARGE(H$7:H$20,L7),H$7:H$20,0))

and this one in M8:

=IF(N8=N7,INDEX(INDIRECT("G"&MATCH(M7,G$7:G$20,0)+7&":G
$20"),MATCH(N8,INDIRECT("H"&MATCH(M7,G$7:G$20,0)+7&":H$20"),0)),INDEX(G
$7:G$20,MATCH(N8,H$7:H$20,0)))

Then copy this longer formula down into M9:M20

It works by adjusting the table range to exclude any earlier codes
with the same number of points, and you should get this in L7:N20:

1 azxc 60
2 yuui 60
3 ukjk 55
4 abc 45
5 ujyn 45
6 abce 30
7 dfgr 30
8 tyhj 25
9 eeew 20
10 erwt 20
11 mnhj 20
12 nmbg 20
13 abcd 15
14 rtyu 2


Hope this helps.

Pete
 
I opened by saying that I assumed your data was in G7:H20, i.e. codes
in G7:G20 and points in H7:H20.

If this is not the case then please give full details of where your
(real) data is and where you want the auto-sorted table to be.

Pete
 
It reply the formula you type contain error

Assuming (from your earlier example) that your data is in G7:H20, and
that you want the sorted list on the same rows, then put 1 to 14 in
L7:L20 and this in N7:

=LARGE(H$7:H$20,L7)

and this can then be copied down to N20. Then you can put this formula
in M7:

=INDEX(G$7:G$20,MATCH(LARGE(H$7:H$20,L7),H$7:H$20,0))

and this one in M8:

=IF(N8=N7,INDEX(INDIRECT("G"&MATCH(M7,G$7:G$20,0)+7&":G
$20"),MATCH(N8,INDIRECT("H"&MATCH(M7,G$7:G$20,0)+7&":H$20"),0)),INDEX(G
$7:G$20,MATCH(N8,H$7:H$20,0)))

Then copy this longer formula down into M9:M20

It works by adjusting the table range to exclude any earlier codes
with the same number of points, and you should get this in L7:N20:

1 azxc 60
2 yuui 60
3 ukjk 55
4 abc 45
5 ujyn 45
6 abce 30
7 dfgr 30
8 tyhj 25
9 eeew 20
10 erwt 20
11 mnhj 20
12 nmbg 20
13 abcd 15
14 rtyu 2


Hope this helps.

Pete
 
OH, finally got it, copy and paste with the reply sign '>'
I opened by saying that I assumed your data was in G7:H20, i.e. codes
in G7:G20 and points in H7:H20.

If this is not the case then please give full details of where your
(real) data is and where you want the auto-sorted table to be.

Pete
 
Yeah! Thanks, was figuring for hours what went wrong/
Does that mean that it's working for you now?

Pete
 
Got addition helprequire. How to get all value for a particular cell such
that it appear in individual columns (assuming that the max. points is 10)

code points1 points2 point3 point4 point5
yuui 10.00 60.00 85.00 90.00
mnhj 20.00 40.00 80.00
eeew 20.00 30.00 70.00


code points
abc 45.00
yuui 85.00
abcd 15.00
mnhj 40.00
abce 30.00
azxc 60.00
yuui 10.00
eeew 20.00
rtyu 2.00
mnhj 80.00
tyhj 25.00
ujyn 45.00
dfgr 30.00
ukjk 55.00
yuui 60.00
erwt 20.00
eeew 70.00
mnhj 20.00
nmbg 20.00
eeew 30.00
yuui 90.00
 
Back
Top