vlookup in excel

I'm using vlookup to find data in another sheet.
If the target cell just contains numbers, it works OK. But when the target cells contain formulas, e.g. =RIGHT(B5,4) then all I get is an error. It appears that excel is "seeing" the formulas rather than the value in the cells.

How do I tell excel to take the value in the cell rather than the formula?

Any help appreciated.

Cheers,
Lee

 

When you use a VLOOKUP formula or HLOOKUP formula to locate a calculated LOOKUP value, the formula returns #N/A. This occurs even when a comparison of the LOOKUP value and the value in the lookup table returns TRUE.

This problem occurs because of the precision of your computer, which must represent and manipulate numbers in binary. Microsoft Excel compares the exact binary representation of the numbers, rather than their decimal equivalents, which are displayed on the screen. Therefore, rounding errors can occur in the binary representation of the numbers that are not evident when you compare the decimal values visually.

Type the LOOKUP value, instead of referring to a cell that has been calculated. You can type the LOOKUP value into a cell that is referenced by the formula, or you can type the LOOKUP value directly into the formula.