Sumproduct help with a difference

[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
answer should be 7.

any thoughts??

 

[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

 

[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
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[Max]

Thanks for the compliments, and the refinement!
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik