1

I am trying to take a list of products and extract their manufacturer from the start of the product name. Every product name starts with their manufacturer. This is complicated by the fact that some items have other manufacturers in the body of the name; I need to see what the item starts with. I am dealing with 50,000+ items and 3,000+ manufacturers. The formula I have so far is:

=LOOKUP(1,1/(FIND($C$2:$C$5,B2)),$C$2:$C$5)

This works some times but not others.  For example, in the below worksheet, rows 2 and 3 are correct, but row 4 is not.  The result in cell A4 for “Mike's Fun Toys” (in cell B4) should be “Mike's”, but it comes up as “Fun”.

Worksheet with manufacturers listed in reverse alphabetic order

(Here’s the data in text form that you can copy-and-paste:

+---+---------+-----------------+---------------+
|   |    A    |        B        |       C       |
+---+---------+-----------------+---------------+
| 1 | Formula | Items           | Manufacturers |
+---+---------+-----------------+---------------+
| 2 | Brown   | Brown Cat Toys  | Mike's        |
| 3 | Cat     | Cat Fun Toys    | Fun           |
| 4 | Fun     | Mike's Fun Toys | Cat           |
| 5 |         |                 | Brown         |
+---+---------+-----------------+---------------+

)

But when I change the order of Column C (Manufacturers):

Worksheet with manufacturers listed in (normal) alphabetic order

row 4 becomes correct (“Mike's”), but row 2 goes wrong.

Column A has the formula the whole way down. Expected results:

A2 - Brown
A3 - Cat
A4 - Mike's

How can I get the formula to work regardless of the order of Column C?

4
  • Does that make sense now? Mar 30 '18 at 3:27
  • Thanks for adding the explanation. Please note that the text in the square brackets when you embed an image with ![…] is the “alt text”, alternative text that generally does not appear when the image is being displayed. (Alt text is shown when the image file cannot be retrieved from the web server (e.g., a 404 error) or when image display is turned off in the browser. Some people do this to minimize their network traffic; blind people do it for obvious reasons.) So, by the way, it’s good to specify alt text that describes the image, rather than just commenting on it. … (Cont’d)
    – Scott
    Mar 30 '18 at 4:45
  • (Cont’d) … P.S. I was confused by your statement “I would like a way to constrain the above formula to the beginning of the cell.” because your formula (as I describe in my answer) is correctly looking at the beginning of the product name cell.
    – Scott
    Mar 30 '18 at 4:45
  • I was trying to say that I want to only return matches to the keyword if its in the left most position of the cells string. Because I know all the cells start with the manufacturer. Mar 30 '18 at 12:04
1

For the benefit of readers who do not understand theRyanMark’s formula, it is

  • Finding the position of each of the manufacturers’ names in a given product name.  This results in
    • a value of 1 for the manufacturer’s name that begins the product name (because it appears at the 1st character),
    • higher numbers for other manufacturers’ names that appear in the product name (because they appear later, at higher position numbers), and
    • a #VALUE! error code for manufacturers’ names that don’t appear in the product name.
  • Inverting each of the above (dividing 1 by it), resulting in
    • 1 for the manufacturer’s name that begins the product name (which is the one we want to find),
    • lower positive numbers for other manufacturers’ names that appear in the product name (because 1 divided by a number that’s greater than 1 yields a ratio that is less than 1), and
    • a #VALUE! error code for manufacturers’ names that don’t appear in the product name.
  • Using LOOKUP to find the 1 in the above.

For example, for cell A4 (corresponding to “Mike's Fun Toys” in cell B4), in the first image, we get, in order,

  • 1, because “Mike's” (C2) begins “Mike's Fun Toys”,
  • 8, because “Fun” (C3) appears at the 8th character of “Mike's Fun Toys”, and
  • #VALUE! and #VALUE!, because “Cat” (C4) and “Brown” (C5) don’t appear in “Mike's Fun Toys”.

Inverting that results in 1, 0.125 (1/8), #VALUE! and #VALUE!.  Then it looks for the 1 in that array.  This “should” work, because 1 is the first result, and “Mike's” is the first name in Column C.

The problem can be seen in the help page for LOOKUP:

For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order.

and clearly 1 followed by 0.125 is not sorted in ascending order.

As the LOOKUP suggests, we can solve this using MATCH.  The formula you want, which uses the same basic approach as your formula (except without the inverting, which is unnecessary), is

=INDEX($C$2:$C$5, MATCH(1, FIND($C$2:$C$5,$B2), 0))

The third argument to MATCH is called “match_type”.  I have set it to 0 here, which means that MATCH will look for the first element in the array that is exactly 1, and will not assume that the array is sorted.

This is an array formula, so you have to press Ctrl+Shift+Enter when you enter it.

1
  • Great explanation. <applause> and +1. Mar 30 '18 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.