Question: Regarding Lookups

[Derrick]

Why won't Lookup recognize C3 x 4.1 when the cell im looking it up in has...
C3 x 4.1 exactly?
also in that column are 2 x 4, 2 x 2 x .125, and many more, but Lookup() can
'lookup' these cells.

can anyone offer an explanation? or a method of fixing this?

thanks

 

[T. Valko]

C3 x 4.1

Is that the literal TEXT string C3 x 4.1 or does that mean cell C3 times
4.1?

If you're using the LOOKUP( ) function then the lookup_vector *must* be
sorted in ascending order otherwise there's no telling what result you may
get.

You can probably use the VLOOKUP( ) and set the range_lookup argument to 0
or FALSE to find exact matches only.

 

[Dave Peterson]

You have an active thread elsewhere.

Why won't Lookup recognize C3 x 4.1 when the cell im looking it up in has...
C3 x 4.1 exactly?
also in that column are 2 x 4, 2 x 2 x .125, and many more, but Lookup() can
'lookup' these cells.

can anyone offer an explanation? or a method of fixing this?

thanks

 

[Derrick]

hi.
yes, that is the literal text.
- "C3 x 4.1"

I've tried using the Match() function, with the match_type = 0, but it still
doesnt work. for cells with "10 x 2 x .125" it will find "10 x 2" and return
the value corresponding to that one.

 

[Derrick]

thanks. . i know.

i've changed it to a VLOOKUP() formula, but i've come across 2 problems.
1 my info that im looking up in on the left side of the description, and as
far as i know, vlookup always looks on column A (or the left side)

so:

Item Description
1 C3 x 4.1
2 C4 x 6.2
3 2 x 4
4 2 x 2 x .125

i want to return the item number, using the description. it can't be the
other way around because i have the description in a dropdown list - ppl wont
know what steel it is by the item number.

2 im stil getting a #N/A error, which doesnt make sense, because the
dropdown list has its descriptions copied into it from the sheet im trying to
compare it to. so they should be exactly the same.

 

[RagDyer]

Elementary question - are you enclosing your lookup values in quotes?

How about posting your actual formula(s).

 

[RagDyer]

With your dropdown list in C1, try something like this:

=INDEX(A2:A5,MATCH(C1,B2:B5,0))

 

[Dave Peterson]

My message was more of a warning to potential posters. Since you made multiple
posts to various newsgroups, you may already have your answer--or at least
similar responses.

I was warning them that they may be wasting their time.

 

[RagDyer]

I should have picked up on that, but that would mean reading the entire
thread before opening my mouth, which I guess doesn't happen too often.<bg>