Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Lookup
I have a database that I need to do some index/matching on. One
result would be the value of the lowest score for today's date. The other would be the center (CREG, CHI, EKA etc) with the lowest score. I am using the small function because I will eventually need to adjust my formula for the 2nd, 3rd and 4th lowest scores. I have tried for some time with a formula =INDEX(B1:F1,MATCH(TODAY (),A1:A7),SMALL(B2:F7,1),B2:F7) data below starts in cell A1 Date CREG CHI EKA ELK FRO 11/06/08 21.9 -24.7 56.4 39.8 3.2 11/07/08 24.0 93.2 10.8 79.8 72.8 11/10/08 4.1 55.9 85.4 -188.7 49.6 11/11/08 36.0 77.8 35.4 -42.0 -10.9 11/12/08 11/13/08 Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Lookup
Since B1:F1 is one-dimensional INDEX only needs one more argument. I
assume you would like four additional columns to the right, headed with 1, 2, 3, 4 in, say K1:N1. Each column will report the center with the nth lowest number, with one set for each day. The following formula in K2 can be copied down and across: INDEX($B$1:$F$1,MATCH(SMALL($B2:$F2,K$1),$B2:$F2,0 )) HTH Kostis Vezerides On Nov 13, 9:28*pm, SteveR wrote: I have a database that I need to do some index/matching on. *One result would be the value of the lowest score for today's date. *The other would be the center (CREG, CHI, EKA etc) with the lowest score. I am using the small function because I will eventually need to adjust my formula for the 2nd, 3rd and 4th lowest scores. I have tried for some time with a formula *=INDEX(B1:F1,MATCH(TODAY (),A1:A7),SMALL(B2:F7,1),B2:F7) data below starts in cell A1 Date * *CREG * *CHI * * EKA * * ELK * * FRO 11/06/08 * * * *21.9 * *-24.7 * 56.4 * *39.8 * *3.2 11/07/08 * * * *24.0 * *93.2 * *10.8 * *79.8 * *72.8 11/10/08 * * * *4.1 * * 55.9 * *85.4 * *-188.7 *49.6 11/11/08 * * * *36.0 * *77.8 * *35.4 * *-42.0 * -10..9 11/12/08 11/13/08 Thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Lookup
On Nov 13, 11:39*am, vezerid wrote:
Since B1:F1 is one-dimensional INDEX only needs one more argument. I assume you would like four additional columns to the right, headed with 1, 2, 3, 4 in, say K1:N1. Each column will report the center with the nth lowest number, with one set for each day. The following formula in K2 can be copied down and across: INDEX($B$1:$F$1,MATCH(SMALL($B2:$F2,K$1),$B2:$F2,0 )) HTH Kostis Vezerides On Nov 13, 9:28*pm, SteveR wrote: I have a database that I need to do some index/matching on. *One result would be the value of the lowest score for today's date. *The other would be the center (CREG, CHI, EKA etc) with the lowest score. I am using the small function because I will eventually need to adjust my formula for the 2nd, 3rd and 4th lowest scores. I have tried for some time with a formula *=INDEX(B1:F1,MATCH(TODAY (),A1:A7),SMALL(B2:F7,1),B2:F7) data below starts in cell A1 Date * *CREG * *CHI * * EKA * * ELK * * FRO 11/06/08 * * * *21.9 * *-24.7 * 56.4 * *39.8 * *3..2 11/07/08 * * * *24.0 * *93.2 * *10.8 * *79.8 * *72.8 11/10/08 * * * *4.1 * * 55.9 * *85.4 * *-188.7 *49.6 11/11/08 * * * *36.0 * *77.8 * *35.4 * *-42.0 * -10.9 11/12/08 11/13/08 Thanks, Steve- Hide quoted text - - Show quoted text - Thanks Kostis , but that does not include anything for the date lookup I need. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Lookup
This is because I assumed you would like these results for every day
and I misread your use of TODAY(). So in K2, =INDEX($B$1:$F$1,MATCH(SMALL(INDEX($B$2:$F$7,MATCH (TODAY(),$A$2:$A $7,0),0),K$1),INDEX($B$2:$F$7,MATCH(TODAY(),$A$2:$ A$7,0),0),0)) HTH Kostis Thanks Kostis , but that does not include anything for the date lookup I need. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Lookup
On Nov 13, 12:24*pm, vezerid wrote:
This is because I assumed you would like these results for every day and I misread your use of TODAY(). So in K2, =INDEX($B$1:$F$1,MATCH(SMALL(INDEX($B$2:$F$7,MATCH (TODAY(),$A$2:$A $7,0),0),K$1),INDEX($B$2:$F$7,MATCH(TODAY(),$A$2:$ A$7,0),0),0)) HTH Kostis Thanks Kostis , but that does not include anything for the date lookup I need.- Hide quoted text - - Show quoted text - Thanks Kostis- that worked perfectly! Now that I have the four centers with the lowest scores, how can i actually return their scores ? I tried to amend your fomula but did not come up with their scores Thanks, Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Lookup
On Nov 13, 1:44*pm, SteveR wrote:
On Nov 13, 12:24*pm, vezerid wrote: This is because I assumed you would like these results for every day and I misread your use of TODAY(). So in K2, =INDEX($B$1:$F$1,MATCH(SMALL(INDEX($B$2:$F$7,MATCH (TODAY(),$A$2:$A $7,0),0),K$1),INDEX($B$2:$F$7,MATCH(TODAY(),$A$2:$ A$7,0),0),0)) HTH Kostis Thanks Kostis , but that does not include anything for the date lookup I need.- Hide quoted text - - Show quoted text - Thanks Kostis- that worked perfectly! Now that I have the four centers with the lowest scores, how can i actually return their scores ? I tried to amend your fomula but did not come up with their scores Thanks, Steve- Hide quoted text - - Show quoted text - I figured it out (finally) Thanks for your help Kostis ! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Lookup
Glad it worked, thanks for the feedback.
Kostis On Nov 14, 12:49*am, SteveR wrote: On Nov 13, 1:44*pm, SteveR wrote: On Nov 13, 12:24*pm, vezerid wrote: This is because I assumed you would like these results for every day and I misread your use of TODAY(). So in K2, =INDEX($B$1:$F$1,MATCH(SMALL(INDEX($B$2:$F$7,MATCH (TODAY(),$A$2:$A $7,0),0),K$1),INDEX($B$2:$F$7,MATCH(TODAY(),$A$2:$ A$7,0),0),0)) HTH Kostis Thanks Kostis , but that does not include anything for the date lookup I need.- Hide quoted text - - Show quoted text - Thanks Kostis- that worked perfectly! Now that I have the four centers with the lowest scores, how can i actually return their scores ? I tried to amend your fomula but did not come up with their scores Thanks, Steve- Hide quoted text - - Show quoted text - I figured it out (finally) Thanks for your help Kostis ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Lookup/Double Lookup | Excel Worksheet Functions | |||
Double lookup without using vlookup? | Excel Worksheet Functions | |||
Double LOOKUP? Help please... | Excel Worksheet Functions | |||
double lookup | Excel Worksheet Functions | |||
Double lookup | Excel Worksheet Functions |