Match using mixed numeric & alphanumeric list

boot

New Member
Joined
Aug 4, 2005
Messages
17
Hi,
I am having trouble using the MATCH function. The list that is being searched contains both numeric and alphanumeric entries, but only the alphanumeric entries give the correct result. It's almost as though it can't see the numeric entries. I have sorted the list into ascending order.
My formula is below...

[ =IF(ISNA(MATCH(D2,'day'!B2:B152,0)),"NO","YES") ]


I know this is normally something really simple but it's starting to drive me mad.
Any help would be much appreciated.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
A quick test shows no problem with this in my setup. XL97/NT4 (Cell format "General")

Similar problems with VLOOKUP() are solved by ensuring that columns are formatted as text - then selecting the columns in turn and using Data/Text to Columns/finish to force the re-format.
 

boot

New Member
Joined
Aug 4, 2005
Messages
17
Thanks Brian. I had already tried changing the formats but didn't think about using Data/Text to Columns etc. It worked a treat!

The headache is receeding now!

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,118,334
Messages
5,571,589
Members
412,407
Latest member
ElmerCC
Top