LOOKUP value giving erractic results

  • Thread starter Thread starter Mike K
  • Start date Start date
M

Mike K

I have a color in a cell E18 on a worksheet. Let's say its
red. I have colors listed from W35:W53 and I have
corresponding granule numbers string (formatted as text)
that are in AA35:AA53. Isn't red in E18 the lookup value?
If I have red in W46 and 902,555,720 in AA46, then why am
I getting the value from AA45 returned 901,201,546,552 ?
The colors are spelled correctly. Should I be using
lookup or vlookup? I have another cell that is returning
the correct value. Been working on this for two days now.
Any help even, if you point my to more lookup examples
than are in the help section would be appreciated.

=LOOKUP(E18,AJ35:AJ53,AN35:AN53)

Win 2000
Excel 2000


Mike
 
Your ranges don't agree between your description of the problem and the
formula, but I'll assume that's just an error in transcription.

It sounds like you have a space at the end of the word red. Lookup will
search until it finds the value you are looking for _or_ one that is greater
than the one you are looking for in which case it will return the value just
prior to this value (on a sorted list, this is the same as saying it returns
the largest value less than or equal to your target value). If you're
looking for "red" and it finds "red " it will return the value just prior to
"red ". Eliminate the spaces and it should work fine.

HTH
Dave
 
Dave,
I'm at home now, but I'll try to explain it again. I have
a string value in cell E18 which is the next color to be ran. It is
being pulled in from another sheet. I have made a lookup table of 2
columns. Column 1 contains all possible colors and spans W35:W53.
Column 2 contains the granule numbers that make up the color and spans
AA35:AA53. A color in column 1 is directly across from the granule
number string in column 2. So if green is in W44, the granules for
green are in AA44. If red is in W37, the granules for red are in AA37.
I need to read the value in E18, compare it with the color in Column 1
and return the corresponding string in Column 2 and display it in a
separate cell. I didn't know spaces mattered before or after the
value. Sometimes I get the right value, sometimes I don't. I have
toyed with VLOOKUP and got some varying results as well. When the
lookup value changes (E18) it may or may not be right again. I have
alphabetized column 1.

Mike
 
Dave,
I'm at home now, but I'll try to explain it again. I have
a string value in cell E18 which is the next color to be ran. It is
being pulled in from another sheet. I have made a lookup table of 2
columns. Column 1 contains all possible colors and spans W35:W53.
Column 2 contains the granule numbers that make up the color and spans
AA35:AA53. A color in column 1 is directly across from the granule
number string in column 2. So if green is in W44, the granules for
green are in AA44. If red is in W37, the granules for red are in AA37.
I need to read the value in E18, compare it with the color in Column 1
and return the corresponding string in Column 2 and display it in a
separate cell. I didn't know spaces mattered before or after the
value. Sometimes I get the right value, sometimes I don't. I have
toyed with VLOOKUP and got some varying results as well. When the
lookup value changes (E18) it may or may not be right again. I have
alphabetized column 1.

Mike
 
Again, it sounds like you might have a space at the ends of some of the
color strings. I can't be certain without seeing your data of course, but
in my own tests here I have replicated the results you describe by adding
spaces to the words. If this isn't the problem, I think I'd need to see the
actual data to make a better guess.
 
Back
Top