Baffled why formula wont work

**dougmorgan** · 01-25-2016, 07:55 AM

I cannot understand why a simple reference to a cell ,and then multiply by another is returning a "value", all the other cells are working fine ?

Essentially G608 should return O608 *M3(1) (which i appreciate is the same as O608)

All the cells upwards and downwards are working fine, its just G608 to G612 that are returning an error message,the formula in O608 is shown im baffled?

Any pointers? its a very complicated large spreadsheet so i cant really upload it.

Thanks

Doug

**shukla.ankur281190** · 01-25-2016, 08:03 AM

Change you formula with

=IFERROR(INDEX('UK Media'!$BB$9:$BB$1610,MATCH(B608,'UK Media'!$C$9:$C$1911,0)),0)

Ifferror part was retrieving test may be that is why it is happening

**dougmorgan** · 01-25-2016, 08:39 AM

Thanks Ankur,

The formula im using is picking up the required information from the "UK Media " sheet, and if i =O608 it returns the correct value, its just when i add *M3 that the value error is showing, yet in all other cells in the column it works fine?
This is why im baffled?

**MrShorty** · 01-25-2016, 11:25 AM

It can be difficult to debug this sort of thing from a picture (stored in a word processing file).

One thing I notice in the picture, the 1 in M3 is left aligned, which is the default alignment for text (numbers default to right aligned). The first thing I would check is to make sure that M3 contains the number 1 and not the character "1" or the character "1" followed by some non-printing gibberish characters. The =LEN() function can be a useful function for this and/or the ISTEXT() function and/or the CODE() and or the MID(), LEFT(), RIGHT() functions.

List of text manipulation functions: https://support.office.com/en-us/art...__toc309306717