# Excel formula : Find row number of the first match in a list

Situation

I have a column of cells, from U7 to U21, that contain various numbers.

Some numbers may be duplicated in this range.

Requirement

To return the first row number that matches a given value (in the example below the target value is '12' )

Question / Issue

I want to return the row number of the first match. I do NOT want to use an array formula

I have tried the following formula but this will always return zero since it is the lowest number in the true / false array generated or matching cells:

``````=MIN(INDEX((U7:U21=12)*ROW(U7:U21),0,0))
``````

How can I modify this to return the first non-zero number ??

I have tried various other formulas but they either fail or need to be entered as CSA formulas and I do NOT want that.

Any help would be much appreciated as since I know I have done just this in the past but can't remember how I am going mad ! I will no doubt kick myself when someone reminds me.

I also don't want to use VBA or a user-defined-worksheet-function.
LVL 3
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You can use this formula...

``````=INDEX(ROW(U7:U21),MATCH(TRUE,INDEX((U7:U21=12),0),0))
``````

Saurabh...
Author Commented:
Saurabh: Thanks for the quick response.

I am sorry to say that I beat you to it by seconds with the below formula:

``````=MATCH(TRUE,INDEX(((U7:U21=12)*ROW(U7:U21)>0),0,0),0)
``````

Difference:
Mine:
returns a relative cell index (to the U7:U21 range)
Yours:
returns an absolute row index (probably more useful)

Many many thanks for this.

Experts Exchange Solution brought to you by