Vlookup problem - lookup_value is a calculated number 1

[OMoore]

Hi all,

I am creating a vlookup table to pull read information from a named range based on the input value as per normal with vlookup.

I am taking it a small step further in that the number used for my lookup_value is the result of a formula (..=IF(C2="","",LEFT(C2,6))..). I am getting #N/A as an error. When I substitute the actual value returned by the formula instead of the formula, I get a correct result.

How can I make vlookup accepy my required formula for my vlookup_value? (i've noticed that it will accept a simple formula such as =1+1 and return my required value).

Thanks
Owen

 

[KenWright]

LEFT(... returns TEXT not a NUMBER. You need to coerce the data back to numeric, eg:-

(..=IF(C2="","",--LEFT(C2,6))..)

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

Ken - just posting this as a lot of peeps don't know about the -- operator

(..=IF(C2="","",VALUE(LEFT(C2,6)))..)

will work just as well.

Both -- and VALUE() can coerce a string back into a true number

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[OMoore]

Thanks Ken. That's worked perfectly.

Owen

 

[KenWright]

Cheers Geoff:- For anyone curious, J E McGimpsey has an explanation of why -- is often seen in SUMPRODUCT formulas, but principle is the same regardless of application.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

Don't know why but I justy prefer using the VALUE() function - I guess my poor eyes get confused by too many operators and brackets - it just makes it more obvious to me as to which part of the formula is being "co-erced"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Ahhh, but when you find you are seven levels deep with nested functions and you just can't fit your VALUE() one in, you'll be saying 'Thank God for double Unary'


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

[LOL] - on that day I guess I will Ken

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question