Using Index Match with Wildcard on similar names

Originally Posted by dache416

The INDEX MATCH function works great except for those securities with similar names such as ABC Fund, ABC Fund II, Venture Fund I, Venture Fund II, so on and so forth.

You didnt explain how it doesnt work and we have no context in which to discover on our own how its not working. To best help you we need details, clearly explained issues and desired outcomes, sample file(s), etc.

What lookup text are you feeding into the lookup function? I don't see much help with wildcards for a lookup that must distinguish between something like "Apple I" and "Apple II". The only way I can see for the lookup function to correctly distinguish between those two entries is to give the lookup the full text string. This seems like it might be less about this particular function and what value/formula is in BQ1.

Or, reading between the lines, do you really need the function to return something like "there are several possible matches for that input text, user needs to enter a more specific lookup string."

That's means I have to name the securities in my spreadsheet exactly what's in the other spreadsheet who is manage by other person.

Is this a bad or impractical idea? I do not do databases like this, but it sometimes seems to me that the easiest way to avoid this kind of ambiguity and difficulty is to collaborate with the other person so that we are both using the exact same text identifiers for each item. I am optimistic that, once we understand all of the possible variations between your text and your colleague's text, we can come up with a way of "cross-referencing" identifiers, but it seems like that would be unnecessary if you both agree to a common list of identifiers at the start.

I guess what I am saying -- if this were my project, I would find out from my colleague what identifiers he/she is using and use the same identifiers.

Originally Posted by dache416

Hi Zer0Cool,

The formula is working correctly properly. For example the Banana Corp has $1,000. When I use the formula, I get the right total for Banana Corp. The problem happens when there are securities with similar names like Apple I and Apple II. If Apple I has $2,000, and Apple II has $3,000. When I use the formula to pull the Apple I total, I get the right total for Apple I but not for Apple II. Apple II return is the Apple I's total.

Ok I understand a little more of what you are doing here. The problem is you are using a wildcard, which leaves a margin of error.

If your term is "Apple*" and you have "Apple I" and "Apple II" as far as your formula/logic are concerned they are the same thing. You cant later (at least feasibly) tell it to be exact and differentiate between the 2.

So you find yourself in a position where you either have to narrow your scope (by being more specific in your search term) and/or normalize your data (put things in like terms).

When people share workbooks you can avoid difference in data entry by using data validation. If the possible entries could be gotten from a list of possible entries you can use a drop down list to have people type or pick according to only whats on the list. Otherwise you may be able to create a rule or provide them a key with the syntax for how to enter specific values.

Without seeing the structure of your data its hard to give you good advice. You say you want the total for x, y or z but unless I have missed something like your formulas being array entered INDEX/MATCH will stop at the first match it finds. It isnt doing any addition. So if you had Apple I = 2,000 and Apple I = 3,000 the given formulas would be expected to return 1 or the other, not the sum of both.

Originally Posted by Zer0Cool

If your term is "Apple*" and you have "Apple I" and "Apple II" as far as your formula/logic are concerned they are the same thing. You cant later (at least feasibly) tell it to be exact and differentiate between the 2.

So you find yourself in a position where you either have to narrow your scope (by being more specific in your search term)

After seeing your sample the above applies. You need to narrow the scope.

If you need to differentiate between "Bridgepoint Europe" and "Bridgepoint Europe II" then you need search for them specifically.

It appears in your sample as if you are attempting to be able to match despite the "Rollforward" sheet having "LP" or ", LP" on the end of both respectively. Depending on how much variation there is between the 2 sheets in how these entries are made it may simply be easier to clean up your other sheet (normalizing your data) and not using a wildcard at all.

In your sample for instance non of your headers on "FMV" have a comma "," in the header. So on "Rollforward" we could use a helper column and simply truncate the entries if they have a comma to chop off everything from the comma over to the right. That leaves us just with strings that have LP on the end, so you could then check if the 2 characters on the right end of the string are "LP" and if so chop them off. Then either replace the original column A with the cleaned up entries or adjust your formulas to use the helper column instead of column A.

From what I can tell MATCH isnt able to use "NOT" operands in the lookup value, ie: "Bridgeport " & "<>*II*" as something like a COUNTIF should be able.

If you dont normalize your data you can do the opposite and normalize the formulas to include the extra portion of the entries on the other sheet (ex: BQ1&" LP"). If its only 1 part appended on the end this would be feasible, otherwise its not.

This should get you closer to what you are looking to do. It will also make it easier to notice when its not pulling the right value (at least in the small sample).

I am presuming given your previous formula that you are not actually summing values, thus there should only be 1 matching row from which you get the value on the rollforward sheet.

Since SUMIFS can use the "NOT" logic and since the sum of a single number is the same number the formula I used will give the same results as what you had but allow you to adjust for irregularities.

Note that the formula isnt the same all the way across row 78. This is because the things you need to exclude from your search are not consistent in each column. This formula basically allows you to adjust the formula to account for the differences between something like "Bridgepoint Europe" and "Bridgepoint Europe II" while still wild carding but excluding the "II" from the search using "Bridgepoint Europe".

I left your formula in so you may compare results.

See attached.