|  | lookup | | |
| | | crapit |  |
|  Posted: Mon Sep 01, 2008 5:15 am Post subject: lookup |  |
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 |
| |
| | | crapit | |
| | Posted: Mon Sep 01, 2008 5:34 am Post subject: Re: MAX value | |
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?
"crapit" <biggercrap@yahoo.com> wrote in message news:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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 |
| |
| | | Pete_UK | |
| | Posted: Mon Sep 01, 2008 8:56 am Post subject: Re: MAX value | |
Have you looked at the LARGE function? Look in Excel Help for details.
Hope this helps.
Pete
On Sep 1, 8:34 am, "crapit" <biggerc...@yahoo.com> wrote:
| Quote: | 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?
"crapit" <biggerc...@yahoo.com> wrote in messagenews:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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 |
|
| |
| | | crapit | |
| | Posted: Mon Sep 01, 2008 10:33 am Post subject: Re: MAX value | |
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
"Pete_UK" <pashurst@auditel.net> wrote in message
news:198062fd-fa7c-46dc-8ba6-8e3a50f6494c@e53g2000hsa.googlegroups.com...
Have you looked at the LARGE function? Look in Excel Help for details.
Hope this helps.
Pete
On Sep 1, 8:34 am, "crapit" <biggerc...@yahoo.com> wrote:
| Quote: | 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?
"crapit" <biggerc...@yahoo.com> wrote in
messagenews:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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 |
|
| |
| | | crapit | |
| | Posted: Mon Sep 01, 2008 11:07 am Post subject: Re: MAX value | |
| |  | |
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
"Pete_UK" <pashurst@auditel.net> wrote in message
news:f7e0c53e-0672-4849-9e17-7be05d4fb0d8@r66g2000hsg.googlegroups.com...
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
On Sep 1, 1:33 pm, "crapit" <biggerc...@yahoo.com> wrote:
| Quote: | 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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:198062fd-fa7c-46dc-8ba6-8e3a50f6494c@e53g2000hsa.googlegroups.com...
Have you looked at the LARGE function? Look in Excel Help for details.
Hope this helps.
Pete
On Sep 1, 8:34 am, "crapit" <biggerc...@yahoo.com> wrote:
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?
"crapit" <biggerc...@yahoo.com> wrote in
messagenews:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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-
Hide quoted text -
- Show quoted text - |
|
| |
| | | Pete_UK | |
| | Posted: Mon Sep 01, 2008 12:47 pm Post subject: Re: MAX value | |
| | | |
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
On Sep 1, 1:33 pm, "crapit" <biggerc...@yahoo.com> wrote:
| Quote: | 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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:198062fd-fa7c-46dc-8ba6-8e3a50f6494c@e53g2000hsa.googlegroups.com...
Have you looked at the LARGE function? Look in Excel Help for details.
Hope this helps.
Pete
On Sep 1, 8:34 am, "crapit" <biggerc...@yahoo.com> wrote:
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?
"crapit" <biggerc...@yahoo.com> wrote in
messagenews:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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- Hide quoted text -
- Show quoted text - |
|
| |
| | | crapit | |
| | Posted: Mon Sep 01, 2008 12:53 pm Post subject: Re: MAX value | |
| | | |
Err quite confusing, ur H column refer to what?
"Pete_UK" <pashurst@auditel.net> wrote in message
news:ed28d74b-71e4-4af6-b0bc-c0fd228bf101@x41g2000hsb.googlegroups.com...
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
On Sep 1, 2:07 pm, "crapit" <biggerc...@yahoo.com> wrote:
| Quote: | 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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:f7e0c53e-0672-4849-9e17-7be05d4fb0d8@r66g2000hsg.googlegroups.com...
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
On Sep 1, 1:33 pm, "crapit" <biggerc...@yahoo.com> wrote:
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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:198062fd-fa7c-46dc-8ba6-8e3a50f6494c@e53g2000hsa.googlegroups.com...
Have you looked at the LARGE function? Look in Excel Help for details.
Hope this helps.
Pete
On Sep 1, 8:34 am, "crapit" <biggerc...@yahoo.com> wrote:
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?
"crapit" <biggerc...@yahoo.com> wrote in
messagenews:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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-
Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text - |
|
| |
| | | crapit | |
| | Posted: Mon Sep 01, 2008 1:27 pm Post subject: Re: MAX value | |
| | | |
It reply the formula you type contain error
"Pete_UK" <pashurst@auditel.net> wrote in message
news:ed28d74b-71e4-4af6-b0bc-c0fd228bf101@x41g2000hsb.googlegroups.com...
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
On Sep 1, 2:07 pm, "crapit" <biggerc...@yahoo.com> wrote:
| Quote: | 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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:f7e0c53e-0672-4849-9e17-7be05d4fb0d8@r66g2000hsg.googlegroups.com...
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
On Sep 1, 1:33 pm, "crapit" <biggerc...@yahoo.com> wrote:
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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:198062fd-fa7c-46dc-8ba6-8e3a50f6494c@e53g2000hsa.googlegroups.com...
Have you looked at the LARGE function? Look in Excel Help for details.
Hope this helps.
Pete
On Sep 1, 8:34 am, "crapit" <biggerc...@yahoo.com> wrote:
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?
"crapit" <biggerc...@yahoo.com> wrote in
messagenews:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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-
Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text - |
|
| |
| | | crapit | |
| | Posted: Mon Sep 01, 2008 1:44 pm Post subject: Re: MAX value | |
| | | |
OH, finally got it, copy and paste with the reply sign '>'
"Pete_UK" <pashurst@auditel.net> wrote in message
news:40af1274-b6a3-4aef-9c28-4aed0df16605@c65g2000hsa.googlegroups.com...
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
On Sep 1, 3:53 pm, "crapit" <biggerc...@yahoo.com> wrote:
| Quote: | Err quite confusing, ur H column refer to what?
"Pete_UK" <pashu...@auditel.net> wrote in message
news:ed28d74b-71e4-4af6-b0bc-c0fd228bf101@x41g2000hsb.googlegroups.com...
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
On Sep 1, 2:07 pm, "crapit" <biggerc...@yahoo.com> wrote:
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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:f7e0c53e-0672-4849-9e17-7be05d4fb0d8@r66g2000hsg.googlegroups.com...
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
On Sep 1, 1:33 pm, "crapit" <biggerc...@yahoo.com> wrote:
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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:198062fd-fa7c-46dc-8ba6-8e3a50f6494c@e53g2000hsa.googlegroups.com...
Have you looked at the LARGE function? Look in Excel Help for details.
Hope this helps.
Pete
On Sep 1, 8:34 am, "crapit" <biggerc...@yahoo.com> wrote:
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?
"crapit" <biggerc...@yahoo.com> wrote in
messagenews:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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-
Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text - |
|
| |
| | | Pete_UK | |
| | Posted: Mon Sep 01, 2008 1:48 pm Post subject: Re: MAX value | |
| | | |
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
On Sep 1, 2:07 pm, "crapit" <biggerc...@yahoo.com> wrote:
| Quote: | 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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:f7e0c53e-0672-4849-9e17-7be05d4fb0d8@r66g2000hsg.googlegroups.com...
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
On Sep 1, 1:33 pm, "crapit" <biggerc...@yahoo.com> wrote:
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
"Pete_UK" <pashu...@auditel.net> wrote in message
news:198062fd-fa7c-46dc-8ba6-8e3a50f6494c@e53g2000hsa.googlegroups.com....
Have you looked at the LARGE function? Look in Excel Help for details.
Hope this helps.
Pete
On Sep 1, 8:34 am, "crapit" <biggerc...@yahoo.com> wrote:
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?
"crapit" <biggerc...@yahoo.com> wrote in
messagenews:Oi6r7JADJHA.4712@TK2MSFTNGP05.phx.gbl...
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-
Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text - |
|
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
