# Sumproduct help with a difference

K

#### k.mca

hi All,

i understand how sumproduct works but i want to return the column
number of the first true(1) citriea within the sumproduct function.
see below example

Result row 2 4 6 7 5 6 6 (range
is a2:a7)
Data row 2 3 4 3 3 4 2 (range is
b2:b7)

=sumproduct(--(b2:b7=3),--(a2:a7)) this formula will return
=sumproduct({0,1,0,1,1,0,0}, {2,4,6,7,5,6,6}) and the answer of 16

what i want to do is work out formula that will return the only the
3rd (1(True) within the sumproduct function) value therefore the

any thoughts??

K

#### k.mca

sorry guys,

i want the formula linked to cell c2, if i change c3 to 1 c2 will
return 4, if i change c3 to 2 c2 should show 7 and if i change c3 to 3
c2 should show 5.

hope it makes sense

M

#### Max

Your data as originally posted is assumed in A2:B8, viz:

2 2
4 3
6 4
7 3
5 3
6 4
6 2

The input cell for the variable is C3, eg: 1, 2 , 3

Place this in C2, array-enter the formula,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(A2:A8,SMALL(IF((B2:B8=3)*ROW(1:7)<>0,ROW(1:7)),C3))

C2 will return the results that you seek, as described below
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik