0

I have multiple product families I'm working with, and each family has multiple products within. Some families have more products than others, all product names are unique. On one sheet named "prod fams", I have a lookup table like this:

| Product names                   | Product family |
|:----                            |:------:        |
| Prod1, Prod2                    | Gizmos         |
| Thing1, Thing2, Thing3, Thing4  | Widgets        |

In another sheet I have a database of field failures. Each row is a failure instance and has a product associated with it like this:

| Case number | Date   | Product Name |
|:----        |:------:|:------:      |
| 1           | 2/21/22| Prod2        |
| 2           | 2/22/22| Thing3       |

I want to add a fourth column to this database for Product Family. My formula is a Vlookup like so:

=VLOOKUP("*"& C2 &"*",'prod fams'!A$1:B$12,2,FALSE)

So I find Prod2 (in this case) in the Product Names column in the look-up table - note the asterisk wildcards - and return the Product Family. This works for "Gizmos" not for "Widgets." For Widgets, I get #N/A. What is going on here?

Improve this question
1
  • For what it's worth, the second table's formatting shows up fine in the preview, then comes out here looking like a dumpster fire.
    – AnthonyJS
    Commented Feb 25, 2022 at 0:41

2 Answers 2

Reset to default
1

It is not the wildcard aspect. VLOOKUP() itself can only use the first 255 characters.

(There are ways to handle that, but that isn't the point here.)

INDEX/MATCH has the same problem.

However, the simplest solution is to use XMATCH() instead (as a much newer function, it is able to handle longer strings).

That would allow you to keep your original structure, if that is important to you.

Improve this answer
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Feb 27, 2022 at 16:05
0

I figured it out. Apparently, Excel wildcards can only search a certain number of characters. My work-around was to make another row in the lookup table for "Widgets" and put half the products in the first row and the rest in the second.

I'm not certain what that character limit is, but i suspect it's 255. The reason being that the rows I had to split up were ~280 characters (and 255 is a max-value byte in decimal).

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .