I'm using Excel 2007 and am using a look up formula =isna(match(cell, cell range, 0)) to see whether a number in one column
occurs in another column.
I have been doing this process successfully for over a year on Excel 2003 and more
recently on Excel 2007. This is until today, when it's stopped doing what I expect - could I have changed a setting?
The correct number format for this sheet should be 0000 (so now '67' will appear as '0067' or '290' will appear as '0290'
etc.) So I have applied this Custom Format to my columns: '0000'
After applying the Custom Format to the spreadsheet the numbers appear correctly - 560 now appears as 0560 - BUT in the
formula bar they still only appear as the original format - so 560 is STILL 560 rather than 0560 as it should be!
As a result of this, my look up is not looking for the number in the new format, it's still looking for the number in its
original format - so a look up for 0560 is actually returning results for anything with 560 in it: 560, 5600, 5601, 56000,
I need my look up to be looking for 0560 NOT 560.
How do I force the numbers to become 4 digits in the formula bar
so that my look up for 0560 only finds a match with a cell containing 0560 and not 560, 5600, 5601, 56000, 56007 etc.