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


M

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
 
Ad

Advertisements

J

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
 
M

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top