If cell contains text contained in a named range return the row number

Hi,

Do you mean the relative or absolute row number? For example, if, in the the range A6:A20, cell A9 met your criteria, would you want 4 or 9 to be returned?

Perhaps you could post a workbook with an example and your desired result clearly outlined.

Regards

Thanks.

And by "contains a text string" do you mean that the entire contents of the cell match one of the values in the Named Range, or just that some part of it does?

For example, if your Named Range consists of 3 entries: "cat", "dog" and "mouse", would a cell containing "123cat456" be a valid match according to your criteria? Or must a cell contain precisely either "cat", "dog" or "mouse" for it to be considered a match?

Regards

=LOOKUP(2,1/(ISNUMBER(MATCH(A6:A20,Range1,0))),ROW(A6:A20))

where Range1 is the Named Range in question, assumed to be either a single-row or single-column vector (and not, for example, a 2-dimensional grid).

You don't say which row number should be preferred if there is more than one such match: this formula will return the last.

Regards

You're welcome!