Why?

[dantheram]

I have set up a vlookup as follows;- vlookup($A15,A3:A5,1,FALSE)

the problem lies with the lookup value, A15, this cell is not a value, it is a formula which returns a value i.e =c5 (c5 being the value i want to look up).

How do i tell vlookup to ignore the formula and use the returned value?

 

[GlennUK]

You don't need to tell vlookup that, it does it automatically. There must be some other error going on. Like numbers stored as text, etc etc.

 

[dantheram]

i thought so, but if i write in the value manually it works, if i leave the formula it doesn't.

 

[GlennUK]

And what is the value? Does it seem to be numeric? Or is it text? If text, have you checked for trailing spaces?

 

[T. Valko]

I have set up a vlookup as follows;- vlookup($A15,A3:A5,1,FALSE)

the problem lies with the lookup value, A15, this cell is not a value, it is a formula which returns a value i.e =c5 (c5 being the value i want to look up).

How do i tell vlookup to ignore the formula and use the returned value?

The VLOOKUP doesn't evaluate the formula in A15, it "sees" the value returned by that formula.

Here's another way to do what you're trying to do:

=IF(COUNTIF(A3:A5,A15),A15,"Not Found")

 

[Jonmo1]

Then I would think the value in C5 is not EXACTLY what you think it is...

Is it a number or a text value?

If it's a number, then it's probalby a "number stored as text"

Try changing the formula in A1 from
=C5
to
=C5+0

 

[dantheram]

this is very bizarre now.

the lookup is working except it wont return 1 of the 3 possible values.

for example, Red, white, blue are all in the table array.

red and blue are returned fine but white = #N/A

 

[GlennUK]

Check for trailing spaces in either the table or the test value.

 

[dantheram]

none in any values, i just dont understand why it works fine for 2 and not the 3rd

Apologies, you're right their was a space, all is working now.

Many thanks for everyones input.

Dan