lookups referring to formulas
 

I am trying to use the vlookup function to add detail relating to one figure
in a sales part number. I have item needed in the sales part number parsed
using the a right function.

For some items the parsed value is a letter, but for most it is a number.
The vlookup function works well for those fields where the parsed value is a
letter, but when it is a number(1-8 in this case), it doesn't work.

Is there a solution? Or, is this a limitation of the vlookup function
referring to calculated cells?

My bet it that you have a mismatch between the parsed value you want to lookup
and the leftmost column in the lookup table.

I think I'd try to match that parsed value to whatever you have in that lookup
column.

If you have real numbers, maybe you could convert your formula:

=vlookup(if(isnumber(-(FormulaThatExtractsValue)),--(FormulaThatExtractsValue),
FormulaThatExtractsValue),sheet2!a:c,2,false)





Excel GuRu wrote:

I am trying to use the vlookup function to add detail relating to one figure
in a sales part number. I have item needed in the sales part number parsed
using the a right function.

For some items the parsed value is a letter, but for most it is a number.
The vlookup function works well for those fields where the parsed value is a
letter, but when it is a number(1-8 in this case), it doesn't work.

Is there a solution? Or, is this a limitation of the vlookup function
referring to calculated cells?

--

Dave Peterson

You could try to revise your parsing formula to return "true" numbers:

=IF(ISERROR(--RIGHT(A1)),RIGHT(A1),--RIGHT(A1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Excel GuRu" wrote in message
...
I am trying to use the vlookup function to add detail relating to one figure
in a sales part number. I have item needed in the sales part number parsed
using the a right function.

For some items the parsed value is a letter, but for most it is a number.
The vlookup function works well for those fields where the parsed value is a
letter, but when it is a number(1-8 in this case), it doesn't work.

Is there a solution? Or, is this a limitation of the vlookup function
referring to calculated cells?

Maybe my original post wasn't clear enough. I'm trying to lookup using cell
h9 which contains a formula such as =left(k9,1) If I key in a figure i cell
h9, the lookup formula works, but with the formula calculating, it
doesn't(unless the result of the formula is a letter).

Please let me know if you have any other ideas. We encounter this frequently.

"RagDyer" wrote:

You could try to revise your parsing formula to return "true" numbers:

=IF(ISERROR(--RIGHT(A1)),RIGHT(A1),--RIGHT(A1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Excel GuRu" wrote in message
...
I am trying to use the vlookup function to add detail relating to one figure
in a sales part number. I have item needed in the sales part number parsed
using the a right function.

For some items the parsed value is a letter, but for most it is a number.
The vlookup function works well for those fields where the parsed value is a
letter, but when it is a number(1-8 in this case), it doesn't work.

Is there a solution? Or, is this a limitation of the vlookup function
referring to calculated cells?

=left(k9,1)
will return a string--It could be a letter A-Z or it could be a digit, but it's
still a string to excel.

And the number 9 doesn't equal the string '9 (note the apostrophe).

RagDyer's formula checks to see if that left most character is a digit. If it
is, it returns a number. If it's really text, then it returns the letter (or
special character).

So you could modify RadDyer's formula to return the left most character:

=IF(ISERROR(--left(k9,1)),left(k9,1),--left(k9,1))

or even:

=if(isnumber(-left(k9,1)),--left(k9,1),left(k9,1))


Excel GuRu wrote:

Maybe my original post wasn't clear enough. I'm trying to lookup using cell
h9 which contains a formula such as =left(k9,1) If I key in a figure i cell
h9, the lookup formula works, but with the formula calculating, it
doesn't(unless the result of the formula is a letter).

Please let me know if you have any other ideas. We encounter this frequently.

"RagDyer" wrote:

You could try to revise your parsing formula to return "true" numbers:

=IF(ISERROR(--RIGHT(A1)),RIGHT(A1),--RIGHT(A1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Excel GuRu" wrote in message
...
I am trying to use the vlookup function to add detail relating to one figure
in a sales part number. I have item needed in the sales part number parsed
using the a right function.

For some items the parsed value is a letter, but for most it is a number.
The vlookup function works well for those fields where the parsed value is a
letter, but when it is a number(1-8 in this case), it doesn't work.

Is there a solution? Or, is this a limitation of the vlookup function
referring to calculated cells?



--

Dave Peterson

Thank you Dave, the vlookup function now works. I appreciate the
clarification.

"Dave Peterson" wrote:

=left(k9,1)
will return a string--It could be a letter A-Z or it could be a digit, but it's
still a string to excel.

And the number 9 doesn't equal the string '9 (note the apostrophe).

RagDyer's formula checks to see if that left most character is a digit. If it
is, it returns a number. If it's really text, then it returns the letter (or
special character).

So you could modify RadDyer's formula to return the left most character:

=IF(ISERROR(--left(k9,1)),left(k9,1),--left(k9,1))

or even:

=if(isnumber(-left(k9,1)),--left(k9,1),left(k9,1))


Excel GuRu wrote:

Maybe my original post wasn't clear enough. I'm trying to lookup using cell
h9 which contains a formula such as =left(k9,1) If I key in a figure i cell
h9, the lookup formula works, but with the formula calculating, it
doesn't(unless the result of the formula is a letter).

Please let me know if you have any other ideas. We encounter this frequently.

"RagDyer" wrote:

You could try to revise your parsing formula to return "true" numbers:

=IF(ISERROR(--RIGHT(A1)),RIGHT(A1),--RIGHT(A1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Excel GuRu" wrote in message
...
I am trying to use the vlookup function to add detail relating to one figure
in a sales part number. I have item needed in the sales part number parsed
using the a right function.

For some items the parsed value is a letter, but for most it is a number.
The vlookup function works well for those fields where the parsed value is a
letter, but when it is a number(1-8 in this case), it doesn't work.

Is there a solution? Or, is this a limitation of the vlookup function
referring to calculated cells?



--

Dave Peterson