match

[robzrob]

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?

 

[Wigi]

You can only search within 1 column or row.

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

 

[John C]

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.

 

[Peo Sjoblom]

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

 

[John C]

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.

 

[robzrob]

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









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

 

[robzrob]

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









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