Formula Not Working On Numbers Only

[ExcelRoy]

Hi,

I have the following formula that looks up C27 on a sheet named "Maintenance" column C and returns D27

this works fine for alpha numeric but just numeric it seems not to return anything

ie?:

If I typed 64512 it should return contents of D27 but nothing is returned

But if I type DFIB/2 it works fine

Any ideas

Thnaks

=IFERROR(INDEX(Maintenance!$D$9:$D$2000,MATCH(C27,Maintenance!$C$9:$C$2000,0),0),"")

 

[Scott T]

Are the numbers in column C stored as numbers or text?
If you try to match the number 7 and the value in C is 7 stored as a text value match will return a #N/A error that iferror will turn into a blank.

 

[ExcelRoy]

Hi Scott T,

Both at set to "General" and "" is returned so no error at all

Thanks

 

[Scott T]

If you put a ' in front of a number it will store it as text, or if you use the text function to format it, regardless of if the cell is set to general.

Since you used IFERROR the error will not be displayed as you are telling excel to show "" instead of any error.
Try testing one of the cells. If true is returned the it is stored as a number if false is returned it is stored as text.

=ISNUMBER(C27)

 

[ExcelRoy]

yup both different problem now solved!

Nice little trick

Many thanks for your help