Array Search

[Mary Fetsch]

I have an array of parts numbers and descriptions:
UC10 Surge Protecter
SMT420 Patriot 420
MONI19 19 Inch Monitor

If I enter a value containing one of these parts numbers,
I want the description to appear in the next cell:

UC10a Surge Protecter
SMT420x Patriot 420
UC10b Surge Protecter
SMT420z Patriot 420

The part numbers are always the first part of my values,
but they differ in length. I know I can use lookup
functions to look up a value, and I know I could use the
mid function if my part numbers were the same length.

I also know I could separate the part number and the rest
of my value into separate columns, but I'd rather find a
way to say "if any part of my value is in the array, show
the description". (I already have a lot of values
entered, and it would be a big job to separate them all.)

I'll greatly appreciate any help anyone can give me on
this.

 

[Frank Kabel]

Hi
use
=VLOOKUP(A1,'sheet'!A1:B100,2,0)

 

[Mary Fetsch]

Thanks a lot! I didn't realize I could use that function when my value
didn't exactly match the array field.

 

[Alan Beban]

There seems to be some confusion here. Mary, did you actually get
something that works?

Alan Beban

 

[RagDyer]

Don't know if I follow exactly which has the extra character, the data list
or the lookup value.

Try this, with the value to find entered in E1, with an extra character:

=VLOOKUP(LEFT(E1,LEN(E1)-1),A1:B10,2,0)
--

HTH,

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


Thanks a lot! I didn't realize I could use that function when my value
didn't exactly match the array field.

 

[Mary Fetsch]

The value I'm looking up has the extra character(s). For example, I'm
looking up SMT420xxxx, and it finds SMT420 in the array. Your formula is a
good idea if I only had one extra character, but the number of extra
characters will vary with each entry.

Thanks for your response!

 

[Aladin Akyurek]

You might want to experiment with a smaller begin part of the lookup
values...

=VLOOKUP(LEFT(LookupValue,4)&"*",LookupTable,2,0)

 

[Alan Beban]

Although the presence of MONI19 for a 19 Inch Monitor suggests that
there might be a MONI17, etc.

Alan Beban