INDEX MATCH SMALL


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
 
Ad

Advertisements

C

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: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
 
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 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?
 
C

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:D$10,SMALL(IF($C$2:$C$10=$C$11,ROW($1:$9)),ROW(A1)))


Regards
Claus Busch
 
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 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?
 
C

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
 
Ad

Advertisements

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

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
 
C

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: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
 
Ad

Advertisements

C

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
 
Ad

Advertisements

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top