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

#### BigDelGooner

##### Board Regular
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

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.

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.

..and thank you for getting back to me.

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

That works great, many thanks Jonmo!!

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

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

To make it relative, try

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

Finds the value of F10 in A10:D39

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

Muchos gracias

Replies
3
Views
361
Replies
10
Views
427
Replies
3
Views
350
Replies
1
Views
75
Replies
2
Views
84

1,203,523
Messages
6,055,895
Members
444,832
Latest member
Kauri

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back