Array Search

  • Thread starter Thread starter Mary Fetsch
  • Start date Start date
M

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.
 
Thanks a lot! I didn't realize I could use that function when my value
didn't exactly match the array field.
 
There seems to be some confusion here. Mary, did you actually get
something that works?

Alan Beban
 
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.
 
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!
 
You might want to experiment with a smaller begin part of the lookup
values...

=VLOOKUP(LEFT(LookupValue,4)&"*",LookupTable,2,0)
 
Although the presence of MONI19 for a 19 Inch Monitor suggests that
there might be a MONI17, etc.

Alan Beban
 
Back
Top