VLookup returning #N/A when looking up a cell that has a Formula i

[mickn74]

The lookup formula i am using is VLOOKUP($F$16,AUSTLoads,5,FALSE) it is
searching for an exact match in AUSTloads Sheet

$F$16 is the cell I want it to look at. This cell is also another formula
that returns the Value QCBD.

If I overide the Formula with the Value of QCBD it the lookup works fine.

Can a VLookup formula look up the value in the cell if it is also a formula.
How do i overcome this so it reads the Value and bases the look up on this
value.

Thanks

Michael

 

[Jacob Skaria]

I assume AUSTLoads is a named range. Since it works when referring the text
directly; check whether the value returned by the formula is exactly 'QCBD'
(no spaces).

If there are spaces try this

=VLOOKUP(Trim($F$16),AUSTLoads,5,FALSE)

If this post helps click Yes

 

[mr-tom]

Another quick thing to try would be =EXACT($F$16,Sheet1!$X$Y)

(Change Sheet1!$X$Y to refer to the cell in AUSTLoads which contains the
value QCBD)

Exact returns TRUE is the cells contain the same string and FALSE if they
don't.

I suspect you'll get a false, which is why your lookup is failing.

Jacob's solution will work if $F$16 has extra spaces, but not if these are
in AUSTLoads, in which case you'll need to be a bit creative, e.g. if each
cell in AUSTLoads has a leading space then something like =VLOOKUP("
"&$F$16,AUSTLoads,5,FALSE) might help. " " is double quote space double quote.

HTH

Tom.

 

[mickn74]

Thanks,

Worked a treat

I Appreciate the help

Mick