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


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

Some numbers may be duplicated in this range.


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:


Open in new window

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Saurabh Singh TeotiaCommented:
You can use this formula...


Open in new window

AL_XResearchAuthor Commented:
Saurabh: Thanks for the quick response.

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


Open in new window

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

Many many thanks for this.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
If you want a relative position, all you need is:
AL_XResearchAuthor Commented:
My version of the formula is a 'relative form' of the formula Saurabh provided. I have just selected my answer as well to guide any future reader as to correct answers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.