INDEX MATCH SMALL

[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

 

[Claus Busch]

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

your lookup value in C11. Then in D11:
=INDEX(D$2$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

 

[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 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

Clause I get and error, What is ;KKLEINSTE(WENN AND ZEILE IN THE Formula?

 

[Claus Busch]

Hi,

Clause I get and error, What is ;KKLEINSTE(WENN AND ZEILE IN THE Formula?

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


Regards
Claus Busch

 

[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 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

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?

 

[Claus Busch]

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?

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

 

[Claus Busch]

Hi,

Am Mon, 29 Oct 2012 19:54:02 +0100 schrieb Claus Busch:

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?

have a look for the workbook "jc":
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191


Regards
Claus Busch

 

[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 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

I donthave access to open that from work but Thank you. I hope I can figureit out, that formula is pretty close tho.

 

[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 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

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...

 

[Claus Busch]

Hi,

Am Mon, 29 Oct 2012 14:27:15 -0700 (PDT) schrieb (e-mail address removed):

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

if you sort your table by days and SL, the first 2 rows have the 2
smallest SL


Regards
Claus Busch

 

[Claus Busch]

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

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


Regards
Claus Busch

 

[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

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$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

 

[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))

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

 

[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 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

Thank you Clause this works