Search per criteria and go on incrementing values

[mac_see]

Copy of rajres.xls
	A	B	C	D	E	F	G	H	I	J
1
2
3								0	6	7
4
5	7	1
6	0	1
7	2	1
8	1	1
9	6	1
10	6	2							1
11	6	3							2
12	8	1
13	7	2								1
14	6	4								2
15	0	2						1		3
16	5	1						2		4
17	9	1						3		5
18	4	1						4		6
19	3	1						5
20	0	3						6
new (4)

I want to look up numbers given in row # 3. In this example there are three numbers 0 6 and 7. In my actual data there ten numbers from 0 to 9

Here is the look up criteria. Check the number in cell H3 (ZERO) in column A which correspond to 2 in column B and the moment you get that put the value 1 in the corresponding row in column H. Now go on incrementing the value to 2 3 4 .... till 6 but if you get the same number in Column A that corresponds to 3, then stop incrementing else only increment till 6

Maxi

 

[barry houdini]

Try this formula in H5 copied down and across

=IF(ISNUMBER(H4),IF((($A4<>H$3)+($B4<>3))*(H4<>6),H4+1,""),IF((COUNT(H$4:H4)=0)*($A5=H$3)*($B5=2),1,""))

Note: assumes that you want to have only 1 series of numbers in each column

 

[mac_see]

That was gr8... I really have to sit down and understand how the entire thing works. I thought of various ways and felt that only a vba can do it but your formula based solution rocks.

Thanx again.

 

[mac_see]

I made a little mistake in explaining. I altered your formula according to my desire but I am not able to understand the logic used.

I want to look up numbers given in row # 3. In this example there are three numbers 0 6 and 7. In my actual data there ten numbers from 0 to 9

Here is the look up criteria. Check the number in cell H3 (ZERO) in column A which correspond to 2 in column B and the moment you get that put the value 1 (one cell below) the corresponding row in column H. Now go on incrementing the value to 2 3 4 .... till 6 but if you get the same number in Column A that corresponds to 3, then stop incrementing else only increment till 6

In my example of the number ZERO, it should STOP at 5

Sorry for the confusion

Maxi

 

[barry houdini]

so in column I you only get a 1?

Then change formula in H5 to

=IF(ISNUMBER(H4),IF((($A4<>H$3)+($B4<>3))*(H4<>6),H4+1,""),IF((COUNT(H$4:H4)=0)*($A4=H$3)*($B4=2),1,""))

 

[mac_see]

Thanx