Finding a Value

[Nano]

Hi All,

I have a excel sheet with two columns A and B. This list is quite
long as there are more than 200 rows in it. Now I was to search a
particular row in it.

Lets say I want the row where the value of column A is 'x' and column
B is 'y'. How can I write a formula for that?

Thanking you in anticipation.

Best Regards,
Nano

 

[Bob Phillips]

Try this array formula

=MIN(IF((A1:A200="x")*(B1:B200="y"),ROW(A1:A200)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

 

[Per Jessen]

Hi

You can use an AutoFilter.

Select columns A:B and goto Data > Filter > Autofilter > Filter column A for
x, and column B for Y, then you will only see all rows which meet your
criteria.

Hopes this helps.
....
Per

 

[Nano]

Thanks guys, its work fine.

Regards,
Nano