INDEX MATCH SMALL

  • Thread starter Thread starter jc9972003
  • Start date Start date
J

jc9972003

Can someone help me build a formula that will
Return this answers from the data below My lookup value will be my date i.e 10/24
My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16

.. I think a Index,Match and Samll will work I just cant put it together.

*The 2 Lowest Service Levels on column E11 and E12
*What interval they came in at on Column D11 and D12
*Howmany Calls came in during those intervals on Column F11 and F12
C D E F
Date Interval SL Calls
1 10/24/2012 3:00 PM 65% 16
2 10/24/2012 4:00 PM 20% 17
3 1/24/1900 5:00 PM 87% 18
4 10/25/2012 6:00 PM 60% 19
10/25/2012 7:00 PM 16% 20


Lookup Value 10/24/2012
11 What interval What SL Howmany Calls
12 What interval What SL Howmany Calls
 
Hi,

Am Mon, 29 Oct 2012 10:33:02 -0700 (PDT) schrieb (e-mail address removed):
*The 2 Lowest Service Levels on column E11 and E12
*What interval they came in at on Column D11 and D12
*Howmany Calls came in during those intervals on Column F11 and F12
C D E F
Date Interval SL Calls
1 10/24/2012 3:00 PM 65% 16
2 10/24/2012 4:00 PM 20% 17
3 1/24/1900 5:00 PM 87% 18
4 10/25/2012 6:00 PM 60% 19
10/25/2012 7:00 PM 16% 20


Lookup Value 10/24/2012
11 What interval What SL Howmany Calls
12 What interval What SL Howmany Calls
Click to expand...

your lookup value in C11. Then in D11:
=INDEX(D$2:D$10;KKLEINSTE(WENN($C$2:$C$10=$C$11;ZEILE($1:$9));ZEILE(A1)))
and enter the array formula with CTRL+Shift+Enter and drag it to the
right and 2 rows down.


Regards
Claus Busch
 
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together.*The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervalson Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls
Click to expand...

Clause I get and error, What is ;KKLEINSTE(WENN AND ZEILE IN THE Formula?
 
Hi,
Clause I get and error, What is ;KKLEINSTE(WENN AND ZEILE IN THE Formula?
Click to expand...

sorry that i sent you the formula in german language. Here is the
correct formula:
=INDEX(D$2:D$10,SMALL(IF($C$2:$C$10=$C$11,ROW($1:$9)),ROW(A1)))


Regards
Claus Busch
 
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together.*The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervalson Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls
Click to expand...

I get #NA Even after I hit cntrl shift enter, Does the ,Row(A1))) Supose tobe on A1 Even tho there is no data there?
 
Hi,

Am Mon, 29 Oct 2012 11:49:24 -0700 (PDT) schrieb (e-mail address removed):
I get #NA Even after I hit cntrl shift enter, Does the ,Row(A1))) Supose to be on A1 Even tho there is no data there?
Click to expand...

Row(A1) = 1 that returns you the smallest value. If you drag it down it
will change to Row(A2) and gives you the second smallest.
is in C11 your Lookup value?


Regards
Claus Busch
 
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together.*The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervalson Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls
Click to expand...



I donthave access to open that from work but Thank you. I hope I can figureit out, that formula is pretty close tho.
 
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together.*The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervalson Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls
Click to expand...

I was able to view the file, and the formula is only retreiving the first 2rows of data no matter what SL % or dates I put...
 
Hi,

Am Tue, 30 Oct 2012 08:43:16 +0100 schrieb Claus Busch:
if you sort your table by days and SL, the first 2 rows have the 2
smallest SL
Click to expand...

pls look again for the workbook. I changed it for your expectation


Regards
Claus Busch
 
Hi again,

Am Tue, 30 Oct 2012 10:02:14 +0100 schrieb Claus Busch:
pls look again for the workbook. I changed it for your expectation
Click to expand...

if you are at work and can't lool for the workbook:

Your Lookup date in C11
in E11:
=SMALL(OFFSET($C$1,MATCH($C$11,C2:C10,0),2,COUNTIF(C2:C10,$C$11)),ROW(A1))
in D11:
=INDEX(D$2:D$10,MATCH(C11&E11,$C$2:$C$10&$E$2:$E$10,0))
in F11:
=INDEX(F$2:F$10,MATCH(C11&E11,$C$2:$C$10&$E$2:$E$10,0))

The formulas in D11 and F11 are array formulas to enter with
CTRL+Shift+Enter


Regards
Claus Busch
 
Hi,

Am Tue, 30 Oct 2012 10:12:33 +0100 schrieb Claus Busch:
=SMALL(OFFSET($C$1,MATCH($C$11,C2:C10,0),2,COUNTIF(C2:C10,$C$11)),ROW(A1))
Click to expand...

change the formula in E11 to:
=SMALL(OFFSET($C$1,MATCH($C$11,$C$2:$C$10,0),2,COUNTIF($C$2:$C$10,$C$11)),ROW(A1))


Regards
Claus Busch
 
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together.*The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervalson Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls
Click to expand...


Thank you Clause this works
 
Back
Top