search a cell against a list of values and return the value

  • Thread starter Thread starter Richard Norris
  • Start date Start date
R

Richard Norris

I'm trying to find a function or method of looking at the
content of a cell and if a match is found against a list
then return that value.

eg.
From Column A, compare against a list of groupings and
return value of a match.

A B
1 Paper pad Paper
2 binder file Binder
3 Post it note Notes
4 A4 envelopes Envelopes
5 Blue Pens Pens

Compare list
Paper
Binder
Notes
Envelopes
Pens
 
You can use a find next macro (looking for XLPART) to look at each cell in
the compare list and try to find in the table. If found then the found list
offset (,1) could be the compare value. Have a look at the example in vba
INDEX for FIND. There is an example for the find part. Then place in a
for/each loop.
 
Richard Norris wrote...
I'm trying to find a function or method of looking at the content
of a cell and if a match is found against a list then return that
value.

eg.
From Column A, compare against a list of groupings and return
value of a match.

___A____________B
1__Paper pad_____Paper
2__binder file_____ Binder
3__Post it note____Notes
4__A4 envelopes__ Envelopes
5__Blue Pens_____ Pens

Compare list
Paper
Binder
Notes
Envelopes
Pens
Click to expand...

There could be ambiguity, such as 'Paper Envelopes', which could matc
either Paper or Envelopes. The following approach would find the firs
match.

Also, A3, 'Post it note', technically has no match in your compare lis
because 'note' isn't an exact match for 'Notes'. Adding fuzzy tex
searching would make this MUCH HARDER, so I'm not going to do so. Ther
are approximate text matching techniques in this newsgroup's archives.

If your compare list were named CmpLst, you could use *array* formula
like

B1:
=INDEX(CmpLst,MATCH(1,COUNTIF(A1,"*"&CmpLst&"*"),0))

If there could only ever be one match in compare list for each entry i
your column A range, you could use the following nonarray formula

=LOOKUP(2,1/COUNTIF(A1,"*"&CmpLst&"*"),CmpLst
 
Back
Top