Simple Function request, in Excel not VBA

[hitbid]

Just trying to come up with a formula that helps me do what I want to do in terms of indexing, matching, etc.

I have a row-finding formula that works:
=SMALL(IF($A$2:$A$102=$J2,ROW($A$2:$A$102)),ROW(1:1))

This finds me the Row Number I want.

I have another formula here:
=INDEX(E10:J10,MATCH(M4:R4,E10:J10,0),0)

Notice everywhere that says "10". How do I replace the "10" with the SMALL formula above, so that the formula can be the variable I want. I don't want the "10", I want the Small formula to find the row I want.

I know how to do it in VBA, but the & and the "" method isn't working. Must have forgotten this or I am just missing something.

Thanks.

 

[hitbid]

Hi Hitbid!

I'm sorry, but I didn't understand. Could you post a example?

Markmzz

Sure. Attached is a screen shot. When I put another code here, and there happen to be zeros, it doesn't recognize zeros as matching.

I'd love to hear the explanation of the two lines that pointed out. Can't remember seeing either of these in a formula.
LOOKUP(1/6,1/MMULT
{1;1;1;1;1;1}

1 over MMULT? Weird!

 

[markmzz]

Sure. Attached is a screen shot. When I put another code here, and there happen to be zeros, it doesn't recognize zeros as matching.

Hitbid,

Maybe I'm wrong, but you don't have a match in this case ( 1207968 -> 1207968 - 1198144 <> 1205456, 1201444 <> 1198586, 1194533 <> 1191625 and 1189322 <> 1178612).

I still didn't understand, where is the problem?

Look at this too:

	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	B	C	D	E	F	G	H	I	Enter in ID	Do ID's have a match?
2	532	M	1	897	520	595	134	258	428	761	Yes
3	694	F	2	488	354	552	385	486	579	267	No
4	826	F	2	847	446	419	353	869	203	252	No
5	610	F	2	652	495	243	475	232	387	530	No
6	761	F	2	897	520	595	134	258	428	609	No
7	592	M	1	502	321	217	130	788	868	898	No
8	849	F	2	456	237	0	0	727	765	155	Yes
9	383	F	2	477	450	320	690	101	210	839	No
10	137	F	2	0	0	0	0	0	0	0	Yes
11	604	F	2	780	418	183	270	799	652	386	No
12	155	F	2	456	237	0	0	727	765	307	No
13	617	F	2	219	435	539	373	311	526	137	No
14	0	F	2	0	0	0	0	0	0	273	No
15	157	F	3	606	230	636	285	280	569	744	No
***	*****	**	**	*****	*****	*****	*****	*****	*****	*********	*******************	**

<tbody>
</tbody>

By the way, I can't explain the formula or part, until it is ok.

Markmzz