How can a find the row number of a row meeting specified criteria?

**satania** · 05-25-2012, 01:10 PM

I want to find the row number of a row that meets specified criteria, but I need it to match three different variables in columns B, C, D.

There are mutliple rows, could be any number of such. The cells could have different values like 0, 0, 1; 1,1, 0; 1, 0, 1. and so on. I have the values I want set up in a different temp variables sheet. These came from an index forumal referencing another sheet. I tried using a sumproduct and then using the row formula, but I realized that only gave me the row of the sheet I was in, not the sheet I was referencing in sumproduct.

So is there a way to get the row number of a row containing the right combnation of 1's and 0's?

Thanks for any help.

**NBVC** · 05-25-2012, 01:19 PM

Try:

=MATCH(1,INDEX((range1=criteria1)*(range2=criteria2)*(range3=criteria3),0),0)

and it assumes that the ranges start at row 1... this finds the vertical position within the ranges... so if you want row number and your ranges don't start at row 1, you will need to add number of rows to offset to the formula.