Index Function/Match Function
 

I have 3 columns of non-consecutive data in several rows.

For example:

Column 1 Column 2
Column 3

Widget A Widget A Part 1 100
Widget B Widget B Part 1 125
Widget C Widget C Part 1 175
Widget D Widget D Part 1 225
Widget A Widget A Part 2 250
Widget E Widget E Part 1 300
Widget F Widget F Part 1 325
Widget A Widget A Part 3 50
Widget F Widget F Part 2 500
Widget D Widget D Part 2 450

Step 1: On a new spreadsheet tab, I need a formula to extract the
description of column 2 for the second smallest value of "Widget A" (i.e.
description in column 1). That is, the answer would be Widget A Part 1.

Step 2: I also need a formula (to be placed in the column next to the
formula required in step 1) to extract the second smallest for the answer to
step 1. That is, the answer would be 50.

The index instruction appears to be the function required for step 1 and 2.

Index (a2:c11, ?????, 2)

The trouble I am having is establishing the need for 2 criteria to be true
for the row designation in the index formula.

Index Function/Match Function
 

Step 1.

=INDEX(B2:B11,MATCH(SMALL(IF(((A2:A11="Widget
A")*(C2:C11))0,((A2:A11="Widget A")*(C2:C11))),2),C2:C11,FALSE))

This is an array formula so enter it with Ctrl + Shift + Enter not just
Enter

Step 2.

Step 2: I also need a formula (to be placed in the column next to the
formula required in step 1) to extract the second smallest for the answer
to step 1. That is, the answer would be 50.

50 second smallest?

if you meant smallest try:

=INDEX(C2:C11,MATCH(MIN(IF(((A2:A11="Widget A")*(C2:C11))0,((A2:A11="Widget
A")*(C2:C11)))),C2:C11,FALSE))

Again enter as an array formula

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"M Moore" wrote in message
...
I have 3 columns of non-consecutive data in several rows.

For example:

Column 1 Column 2 Column 3

Widget A Widget A Part 1 100
Widget B Widget B Part 1 125
Widget C Widget C Part 1 175
Widget D Widget D Part 1 225
Widget A Widget A Part 2 250
Widget E Widget E Part 1 300
Widget F Widget F Part 1 325
Widget A Widget A Part 3 50
Widget F Widget F Part 2 500
Widget D Widget D Part 2 450

Step 1: On a new spreadsheet tab, I need a formula to extract the
description of column 2 for the second smallest value of "Widget A" (i.e.
description in column 1). That is, the answer would be Widget A Part 1.

Step 2: I also need a formula (to be placed in the column next to the
formula required in step 1) to extract the second smallest for the answer
to step 1. That is, the answer would be 50.

The index instruction appears to be the function required for step 1 and
2.

Index (a2:c11, ?????, 2)

The trouble I am having is establishing the need for 2 criteria to be true
for the row designation in the index formula.

Index Function/Match Function
 

Step 1.
=INDEX(B2:B11,MATCH(SMALL(IF(((A2:A11="Widget
A")*(C2:C11))0,((A2:A11="Widget A")*(C2:C11))),2),C2:C11,FALSE))

If another widget has a matching value as the 2nd smallest value for widget
A and it is listed before the widget A value you will get incorrect results.
Try it on this data set and you'll see what I mean:

Widget A.....Widget A Part 1.....1000
Widget B.....Widget B Part 1.....250
Widget C.....Widget C Part 1.....175
Widget D.....Widget D Part 1 .....225
Widget A.....Widget A Part 2 .....250
Widget E .....Widget E Part 1.....300
Widget F.....Widget F Part 1......325
Widget A .....Widget A Part 3.....50
Widget F.....Widget F Part 2......500
Widget D .....Widget D Part 2.....450

Try this one:

=INDEX(B2:B11,MATCH(SMALL(IF((A2:A11="Widget
A")*(C2:C11<""),C2:C11),2),IF(A2:A11="Widget A",C2:C11),0))

For Step 2 I would just use a simple Vlookup (unless of course, there may
be duplicates). The lookup value being the result of the above formula:

=VLOOKUP(A15,B2:C11,2,0)

Biff

"Sandy Mann" wrote in message
...
Step 1.

=INDEX(B2:B11,MATCH(SMALL(IF(((A2:A11="Widget
A")*(C2:C11))0,((A2:A11="Widget A")*(C2:C11))),2),C2:C11,FALSE))

This is an array formula so enter it with Ctrl + Shift + Enter not just
Enter

Step 2.

Step 2: I also need a formula (to be placed in the column next to the
formula required in step 1) to extract the second smallest for the answer
to step 1. That is, the answer would be 50.

50 second smallest?

if you meant smallest try:

=INDEX(C2:C11,MATCH(MIN(IF(((A2:A11="Widget
A")*(C2:C11))0,((A2:A11="Widget A")*(C2:C11)))),C2:C11,FALSE))

Again enter as an array formula

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"M Moore" wrote in message
...
I have 3 columns of non-consecutive data in several rows.

For example:

Column 1 Column 2 Column 3

Widget A Widget A Part 1 100
Widget B Widget B Part 1 125
Widget C Widget C Part 1 175
Widget D Widget D Part 1 225
Widget A Widget A Part 2 250
Widget E Widget E Part 1 300
Widget F Widget F Part 1 325
Widget A Widget A Part 3 50
Widget F Widget F Part 2 500
Widget D Widget D Part 2 450

Step 1: On a new spreadsheet tab, I need a formula to extract the
description of column 2 for the second smallest value of "Widget A"
(i.e. description in column 1). That is, the answer would be Widget A
Part 1.

Step 2: I also need a formula (to be placed in the column next to the
formula required in step 1) to extract the second smallest for the answer
to step 1. That is, the answer would be 50.

The index instruction appears to be the function required for step 1 and
2.

Index (a2:c11, ?????, 2)

The trouble I am having is establishing the need for 2 criteria to be
true for the row designation in the index formula.

Index Function/Match Function
 

Thanks for the good catch Biff, I never thought about duplicate values.
Using the result for Step 1 is also good thinking - it simplifies the whole
thing greatly by not having the find the same value twice.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Biff" wrote in message
...
Step 1.
=INDEX(B2:B11,MATCH(SMALL(IF(((A2:A11="Widget
A")*(C2:C11))0,((A2:A11="Widget A")*(C2:C11))),2),C2:C11,FALSE))

If another widget has a matching value as the 2nd smallest value for
widget A and it is listed before the widget A value you will get incorrect
results. Try it on this data set and you'll see what I mean:

Widget A.....Widget A Part 1.....1000
Widget B.....Widget B Part 1.....250
Widget C.....Widget C Part 1.....175
Widget D.....Widget D Part 1 .....225
Widget A.....Widget A Part 2 .....250
Widget E .....Widget E Part 1.....300
Widget F.....Widget F Part 1......325
Widget A .....Widget A Part 3.....50
Widget F.....Widget F Part 2......500
Widget D .....Widget D Part 2.....450

Try this one:

=INDEX(B2:B11,MATCH(SMALL(IF((A2:A11="Widget
A")*(C2:C11<""),C2:C11),2),IF(A2:A11="Widget A",C2:C11),0))

For Step 2 I would just use a simple Vlookup (unless of course, there may
be duplicates). The lookup value being the result of the above formula:

=VLOOKUP(A15,B2:C11,2,0)

Biff

"Sandy Mann" wrote in message
...
Step 1.

=INDEX(B2:B11,MATCH(SMALL(IF(((A2:A11="Widget
A")*(C2:C11))0,((A2:A11="Widget A")*(C2:C11))),2),C2:C11,FALSE))

This is an array formula so enter it with Ctrl + Shift + Enter not just
Enter

Step 2.

Step 2: I also need a formula (to be placed in the column next to the
formula required in step 1) to extract the second smallest for the
answer to step 1. That is, the answer would be 50.

50 second smallest?

if you meant smallest try:

=INDEX(C2:C11,MATCH(MIN(IF(((A2:A11="Widget
A")*(C2:C11))0,((A2:A11="Widget A")*(C2:C11)))),C2:C11,FALSE))

Again enter as an array formula

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"M Moore" wrote in message
...
I have 3 columns of non-consecutive data in several rows.

For example:

Column 1 Column 2 Column 3

Widget A Widget A Part 1 100
Widget B Widget B Part 1 125
Widget C Widget C Part 1 175
Widget D Widget D Part 1 225
Widget A Widget A Part 2 250
Widget E Widget E Part 1 300
Widget F Widget F Part 1 325
Widget A Widget A Part 3 50
Widget F Widget F Part 2
500
Widget D Widget D Part 2 450

Step 1: On a new spreadsheet tab, I need a formula to extract the
description of column 2 for the second smallest value of "Widget A"
(i.e. description in column 1). That is, the answer would be Widget A
Part 1.

Step 2: I also need a formula (to be placed in the column next to the
formula required in step 1) to extract the second smallest for the
answer to step 1. That is, the answer would be 50.

The index instruction appears to be the function required for step 1 and
2.

Index (a2:c11, ?????, 2)

The trouble I am having is establishing the need for 2 criteria to be
true for the row designation in the index formula.