match
 

Trying to do a quite simple MATCH. In B53 I've got

=MATCH(E50,'SET-UP'!A1:M34,0)

E50 is a date, and there is a date exactly matching it in 'SET-UP'!
A1:M34', but it's coming up #N/A. Tried

=MATCH(E50:F50,'SET-UP'!A1:M34,0) where E50:F50 are the 2 merged cells
where the date to be matched is and get #VALUE!

Also tried

=MATCH(E50,'SET-UP'!A:M,0), still get #VALUE!

Is there a rule against referring to other worksheets in a MATCH?

match
 

You can only search within 1 column or row.

This has nothing to do with other sheets or the same sheet.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"robzrob" wrote:

Trying to do a quite simple MATCH. In B53 I've got

=MATCH(E50,'SET-UP'!A1:M34,0)

E50 is a date, and there is a date exactly matching it in 'SET-UP'!
A1:M34', but it's coming up #N/A. Tried

=MATCH(E50:F50,'SET-UP'!A1:M34,0) where E50:F50 are the 2 merged cells
where the date to be matched is and get #VALUE!

Also tried

=MATCH(E50,'SET-UP'!A:M,0), still get #VALUE!

Is there a rule against referring to other worksheets in a MATCH?

match
 

Are you sure everything is formatted the same? In other words, in the array
you are looking in, are the dates considered actual dates? Or are they text?
If you think they might be text, you could try:
=MATCH(E50,--TEXT('SET-UP'!A1:M34,"mm/dd/yyyy"),0)
Enter as an array** formula (CTRL+Shift+Enter)

If you are still getting errors, describe how your data is formatted.

Hope this helps.
--
John C


"robzrob" wrote:

Trying to do a quite simple MATCH. In B53 I've got

=MATCH(E50,'SET-UP'!A1:M34,0)

E50 is a date, and there is a date exactly matching it in 'SET-UP'!
A1:M34', but it's coming up #N/A. Tried

=MATCH(E50:F50,'SET-UP'!A1:M34,0) where E50:F50 are the 2 merged cells
where the date to be matched is and get #VALUE!

Also tried

=MATCH(E50,'SET-UP'!A:M,0), still get #VALUE!

Is there a rule against referring to other worksheets in a MATCH?

match
 

Unmerge the cells, it's bad enough using merged cells in the first place but
if anyone feel they need to then it should be out away of any data and just
for fluff like the top rows or something. Btw, if you merge cells the upper
most left cell is the only one that can have any contents. However you need
to read up on how MATCH works, you need to use a 1x1 row/column range, what
would you expect your formula to return if there was a match, the row or the
column count? You are using A1:M34, that won't work. If you just want to
test a range if a value is in there you can use

=COUNTIF(A1:M34,E50)




--


Regards,


Peo Sjoblom

"robzrob" wrote in message
...
Trying to do a quite simple MATCH. In B53 I've got

=MATCH(E50,'SET-UP'!A1:M34,0)

E50 is a date, and there is a date exactly matching it in 'SET-UP'!
A1:M34', but it's coming up #N/A. Tried

=MATCH(E50:F50,'SET-UP'!A1:M34,0) where E50:F50 are the 2 merged cells
where the date to be matched is and get #VALUE!

Also tried

=MATCH(E50,'SET-UP'!A:M,0), still get #VALUE!

Is there a rule against referring to other worksheets in a MATCH?

match
 

Nevermind that. Your array, specified by 'SET-UP'!A1:M34, can only be one row
or one column. MATCH gives you the relative position of your lookup value in
that row or column, but multiple rows or multiple columns, it will give you
an error.
--
John C


"robzrob" wrote:

Trying to do a quite simple MATCH. In B53 I've got

=MATCH(E50,'SET-UP'!A1:M34,0)

E50 is a date, and there is a date exactly matching it in 'SET-UP'!
A1:M34', but it's coming up #N/A. Tried

=MATCH(E50:F50,'SET-UP'!A1:M34,0) where E50:F50 are the 2 merged cells
where the date to be matched is and get #VALUE!

Also tried

=MATCH(E50,'SET-UP'!A:M,0), still get #VALUE!

Is there a rule against referring to other worksheets in a MATCH?

match
 

On Aug 7, 10:32*pm, John C <johnc@stateofdenial wrote:
Are you sure everything is formatted the same? In other words, in the array
you are looking in, are the dates considered actual dates? Or are they text?
If you think they might be text, you could try:
=MATCH(E50,--TEXT('SET-UP'!A1:M34,"mm/dd/yyyy"),0)
Enter as an array** formula (CTRL+Shift+Enter)

If you are still getting errors, describe how your data is formatted.

Hope this helps.
--
John C



"robzrob" wrote:
Trying to do a quite simple MATCH. *In B53 I've got

=MATCH(E50,'SET-UP'!A1:M34,0)

E50 is a date, and there is a date exactly matching it in 'SET-UP'!
A1:M34', but it's coming up #N/A. *Tried

=MATCH(E50:F50,'SET-UP'!A1:M34,0) where E50:F50 are the 2 merged cells
where the date to be matched is and get #VALUE!

Also tried

=MATCH(E50,'SET-UP'!A:M,0), still get #VALUE!

Is there a rule against referring to other worksheets in a MATCH?- Hide quoted text -

- Show quoted text -

Thanks, Wigi & John, I'm learning a lot tonight! John: They're
definitely 'proper' Excel dates, ie they're integers, but the cells
are formatted as dd.mm.yy.

match
 

On Aug 7, 10:35*pm, John C <johnc@stateofdenial wrote:
Nevermind that. Your array, specified by 'SET-UP'!A1:M34, can only be one row
or one column. MATCH gives you the relative position of your lookup value in
that row or column, but multiple rows or multiple columns, it will give you
an error.
--
John C



"robzrob" wrote:
Trying to do a quite simple MATCH. *In B53 I've got

=MATCH(E50,'SET-UP'!A1:M34,0)

E50 is a date, and there is a date exactly matching it in 'SET-UP'!
A1:M34', but it's coming up #N/A. *Tried

=MATCH(E50:F50,'SET-UP'!A1:M34,0) where E50:F50 are the 2 merged cells
where the date to be matched is and get #VALUE!

Also tried

=MATCH(E50,'SET-UP'!A:M,0), still get #VALUE!

Is there a rule against referring to other worksheets in a MATCH?- Hide quoted text -

- Show quoted text -

Thanks, All, for the advice. Luckily my match values are in one
column so I can amend the array and go ahead.