# Search per criteria and go on incrementing values

#### mac_see

##### Active Member
Copy of rajres.xls
ABCDEFGHIJ
1
2
3067
4
571
601
721
811
961
10621
11632
1281
13721
14642
150213
165124
179135
184146
19315
20036
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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

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.

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

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,""))

Thanx

Replies
35
Views
475
Replies
3
Views
130
Replies
11
Views
307
Replies
10
Views
290
Replies
19
Views
648

1,202,984
Messages
6,052,913
Members
444,612
Latest member
FajnaAli

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back