VLOOKUP when the look up value is the output of a formula

[original goose]

I am running a VLOOKUP, my look up value is a number that is derived from the output of a formula. For some reason the VLOOKUP does not work when the look up value is derived this way. if I manually type in the number it works. It also works when the look up value is a letter, also derived from the output of a formula.

Any suggestions on how I can get this to work when the formula output is a number?

Thanks

 

[Majiggy]

I works when I do it. Can you post your formula? I am thinking that you maybe have a "false" qualifier at the end of the Vlookup and that maybe ... just maybe ... Excel is adding in that trillionth of a decimal point to your formula that it does, and so you need to round your value.

 

[original goose]

Here is the formula. I tried changing the false to true and still got the error

=VLOOKUP(F4,'Hoffman Numbers'!C6:D10,2,FALSE)

 

[Aladin Akyurek]

Here is the formula. I tried changing the false to true and still got the error

=VLOOKUP(F4,'Hoffman Numbers'!C6:D10,2,FALSE)

You have probably a text-number in F4...

=VLOOKUP(F4+0,'Hoffman Numbers'!C6:D10,2,FALSE)

Care to post the formula that you have in F4?

 

[original goose]

I have F4 formated as general, I have tried at as number as well.

Here is the formula for F4:

=G47

And here is the foumula is G47:

=MID($B47,6,2)

And finallly text string in B47
MIDBK2369UPPNXB

I realize I have an extra step involved with G47, I have removed it and still no luck

Thanks for your help

 

[Domenic]

The following formula...

=MID($B47,6,2)

...returns a text value. To coerce it into a numerical value, try the following instead...

=MID($B47,6,2)+0

Hope this helps!

 

[original goose]

Aladin Akyurek
You have solved my problem. By addding a "+0" to the vlookup (=VLOOKUP(F4+0,'Hoffman Numbers'!C6:D10,2,FALSE) it solved the problem and retyrns the correct value.

BTW multiplying by 1 also works.

Do you know why this is the case?

At any rate thanks for your help.

 

[Domenic]

The MID function returns a text value. Since in this case the text value is a number, it can be coerced into a numerical value by any mathematical operation...

=F4+0

=F4*1

=F4/1

=F4^1

=F4*100

...and so on. And, of course, you can always coerce the originating formula, as I've shown in my previous post...

=MID($B47,6,2)+0

Hope this helps!

 

[original goose]

Thanks Domenic, it does help!

 

[Abel_0725]

This 2007 thread help me solve my problem!Deym!Thank you so much!