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

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.
LVL 3
AL_XResearchAsked:
Who is Participating?
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...

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

Open in new window


Saurabh...
0
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:

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

Open in new window


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.
0

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:
=MATCH(12,U7:U21,0)
0
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.