Finding the row a value is in

[z0de]

This seems like it should be easy but I can't find it anywhere.

A user inputs a date in cell b4
There is a table with a row of dates
I want to return what row of the table the date is in.

 

[VoG]

Perhaps

=MATCH(B4,A1:A10,0)

 

[z0de]

That works for the example A1:10 but when I try to use my named table it doesn't work.

=MATCH(B4,Daily,0)

How the table named Daily is made:

=OFFSET(Daily_data!$A$1,1,0,COUNTA(Daily_data!$A:$A)-1,51)

 

[Gerald Higgins]

If Daily is a named range, the Match function should work fine.

Is the required value actually in the target range ?

 

[z0de]

It is a named range, I made a new simple named test range (a1:10) and it worked, does anything look wrong how my range Daily is worked out?

 

[Gerald Higgins]

Same question again

Is the required value actually in the target range ?

 

[z0de]

sorry, yes it is and I think I've cracked it. It looks like match can't be used for more than one column, if I change Daily to be only 1 column across it works.

 

[Gerald Higgins]

Yes, that's probably it. The array must be one dimensional, for example A1:A10 or A1:F1

 

[Jonmo1]

Yes, match can only use 1 column
But you don't have to change your named range...
You can use index to use only 1 column of the named range

=MATCH(B4,INDEX(Daily,0,1),0)

The 1 indicates which column of the named range you want to refer to.

It also looks like your named range begins in row 2 because of the offset by 1 row.

So You'll need to Add 1 to the formula

=MATCH(B4,INDEX(Daily,0,1),0)+1


Hope that helps.