LOOKUP returning value in cell above what I was searching for
 

I am using LOOKUP to pull out some information but it keeps returning
the value in the cell above what I was seaching for. Here is a sample
of my information.
I am not sure if the dashes are messing up the seach.

I use LOOKUP(D3,A1:A500,B1:B500) to get the description and
LOOKUP(D3,A1:A500,C1:C500) to ge the price.

If I type in 048-952 in D3 the query returns

GARDEN TOMATO TONER 8 FL OZ 11.94 when it should return

BABY BEE SHAMPOO BAR 3.5OZ 4.766822

A B
C
015-888 TOMATO TONER 1 OZ SAMPLER 0.759
015-985 GARDEN TOMATO TONER 8 FL OZ 11.94
048-952 BABY BEE SHAMPOO BAR 3.5OZ 4.766822
050-950 CARROT NUTR. NIGHT CREME 1 OZ 5.05971
051-888 CARROT CREME .25 OZ SAMPLER 0.301863
051-949 CARROT NUTRITIVE DAY CREME 2OZ 5.019595
051-999 CARROT NUTRITIVE DAY CR 2OZ EA 0.807375
20660 PEPPERMINT FOOT LOTION 1 KG 4.055966
20681 ORANGE ESSENCE 1 KG
4.919943
20700 LEMON BUTTER 1 KG
6.373578
20720 LAVENDER TOOTHPASTE 1 KG 2.241954
20730 CINNAMINT TOOTHPASTE 1 KG 2.102904
990118 COMB, BABY BEE 0.254
990119 JAR HDPE,8OZ 24-410 CRT NUT LT 0.15941
990128 CAP, MTL, 33/400, ALM.MILK.CRM 0.04086
990130 HONEY 0.00496


Any Help would be appreciated


--
mwrfsu
------------------------------------------------------------------------
mwrfsu's Profile: http://www.excelforum.com/member.php...o&userid=26459
View this thread: http://www.excelforum.com/showthread...hreadid=466511

I think I'd use =vlookup() with a 4th parameter of false (or 0). Then it'll
match on an exact match.

=vlookup(d3,a1:c500,2,false)

In fact, I'd put that table on a dedicated worksheet and use:

=if(d3="","",vlookup(d3,sheet2!a1:c500,2,false))

for =vlookup(), you could look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html

and you may want to look at some descriptions for =index(match()):
http://www.contextures.com/xlFunctions03.html

mwrfsu wrote:

I am using LOOKUP to pull out some information but it keeps returning
the value in the cell above what I was seaching for. Here is a sample
of my information.
I am not sure if the dashes are messing up the seach.

I use LOOKUP(D3,A1:A500,B1:B500) to get the description and
LOOKUP(D3,A1:A500,C1:C500) to ge the price.

If I type in 048-952 in D3 the query returns

GARDEN TOMATO TONER 8 FL OZ 11.94 when it should return

BABY BEE SHAMPOO BAR 3.5OZ 4.766822

A B
C
015-888 TOMATO TONER 1 OZ SAMPLER 0.759
015-985 GARDEN TOMATO TONER 8 FL OZ 11.94
048-952 BABY BEE SHAMPOO BAR 3.5OZ 4.766822
050-950 CARROT NUTR. NIGHT CREME 1 OZ 5.05971
051-888 CARROT CREME .25 OZ SAMPLER 0.301863
051-949 CARROT NUTRITIVE DAY CREME 2OZ 5.019595
051-999 CARROT NUTRITIVE DAY CR 2OZ EA 0.807375
20660 PEPPERMINT FOOT LOTION 1 KG 4.055966
20681 ORANGE ESSENCE 1 KG
4.919943
20700 LEMON BUTTER 1 KG
6.373578
20720 LAVENDER TOOTHPASTE 1 KG 2.241954
20730 CINNAMINT TOOTHPASTE 1 KG 2.102904
990118 COMB, BABY BEE 0.254
990119 JAR HDPE,8OZ 24-410 CRT NUT LT 0.15941
990128 CAP, MTL, 33/400, ALM.MILK.CRM 0.04086
990130 HONEY 0.00496

Any Help would be appreciated

--
mwrfsu
------------------------------------------------------------------------
mwrfsu's Profile: http://www.excelforum.com/member.php...o&userid=26459
View this thread: http://www.excelforum.com/showthread...hreadid=466511

--

Dave Peterson

Hi

Using your data with your formulae, I get the correct result.
As an alternative you could try
=VLOOKUP(D3,A1:C500,2,0) for the description
=VLOOKUP(D3,A1:C500,3,0) for the price

--
Regards
Roger Govier

mwrfsu wrote:
I am using LOOKUP to pull out some information but it keeps returning
the value in the cell above what I was seaching for. Here is a sample
of my information.
I am not sure if the dashes are messing up the seach.

I use LOOKUP(D3,A1:A500,B1:B500) to get the description and
LOOKUP(D3,A1:A500,C1:C500) to ge the price.

If I type in 048-952 in D3 the query returns

GARDEN TOMATO TONER 8 FL OZ 11.94 when it should return

BABY BEE SHAMPOO BAR 3.5OZ 4.766822

A B
C
015-888 TOMATO TONER 1 OZ SAMPLER 0.759
015-985 GARDEN TOMATO TONER 8 FL OZ 11.94
048-952 BABY BEE SHAMPOO BAR 3.5OZ 4.766822
050-950 CARROT NUTR. NIGHT CREME 1 OZ 5.05971
051-888 CARROT CREME .25 OZ SAMPLER 0.301863
051-949 CARROT NUTRITIVE DAY CREME 2OZ 5.019595
051-999 CARROT NUTRITIVE DAY CR 2OZ EA 0.807375
20660 PEPPERMINT FOOT LOTION 1 KG 4.055966
20681 ORANGE ESSENCE 1 KG
4.919943
20700 LEMON BUTTER 1 KG
6.373578
20720 LAVENDER TOOTHPASTE 1 KG 2.241954
20730 CINNAMINT TOOTHPASTE 1 KG 2.102904
990118 COMB, BABY BEE 0.254
990119 JAR HDPE,8OZ 24-410 CRT NUT LT 0.15941
990128 CAP, MTL, 33/400, ALM.MILK.CRM 0.04086
990130 HONEY 0.00496


Any Help would be appreciated