Find row of matched value in range (not just one column)

[BigDelGooner]

Hi all

Is there a way to extend the MATCH formula to look in a range rather than a single column?

As always I'm sure there is a simple way but I am being a bit slow today!

Thanks guys

 

[Jonmo1]

Will there be multiple possible matches for the value?
Say 1 in column A and 1 In Column D ?
If so, which one should be used?

And no, there's not a 'Simple' way.

 

[BigDelGooner]

No there will not be duplicates in the table.

In some ways I'm relieved there is not a simple solution...on the basis i am not being stupid.

 

[BigDelGooner]

..and thank you for getting back to me.

 

[Jonmo1]

With the assumption of only 1 possible match
With the lookup value in F1, searching for match in A1:D30

=SUMPRODUCT((A1:D30=F1)*ROW(A1:D30))

 

[BigDelGooner]

That works great, many thanks Jonmo!!

 

[BigDelGooner]

Ahh...just noticed that this returns the absolute row rather than the row of range..?

 

[Jonmo1]

Can you give example of the range, value to find, and what you want to DO with that row# once you have it?

 

[Jonmo1]

To make it relative, try

=SUMPRODUCT((A10:D39=F10)*(ROW(A10:D39)-ROW(A10)+1))

Finds the value of F10 in A10:D39

 

[BigDelGooner]

Sorry I've been away from my desk, thank you v much for this Jonmi that works perfectly.

Muchos gracias