Index partial Matching with at least 3 characters matching per criteria

I believe sumproduct would be better for this (with index of course) But I don't quite understand the sample file.

You can do partial matches with the * character which basically means any amount of characters, 0-infinity. So if for example, you want to match AIC to a cell containing it somewhere within, you would use something like:

match("*AIC*",array,0)

However, for multiple criteria, I find that sumproduct is the go to function, since it can determine if there are matches with multiple criteria. Also, you can decide if you want to put ors in there, or ands (which I am also not sure about from the given file). It seems to me that you want D to match A, then either E or F has to match B, but I am not totally sure.

Anyway, given your desired results, index(iferror(match,match)) might be the way to go, if I understand your file correctly.
However, the way your sample data is setup is extremely limiting, and you would need to reformat your data before being able to do any matching (to my knowledge anyway). You might be able to come up with some crazy formula replacing spaces with non-spaces, but it would be easier if you format your data in a way that you would want it to be.

I got as far as this array formula for testing purposes and realized your data had spaces in weird spots, so I stopped there.

Please Login or Register  to view this content.

entered with CTRL+SHIFT+ENTER

I also used a helper table adding * before and after the given match criterias with this, from J2 to L12

="*"&D2&"*"

If you separate your match criteria columns into what you want to actually match, this formula will work, if you nest enough matches.

It will become extremely data hungry very fast though, as will any solution since they will all be array based.

What exactly do you want this for, a better solution might be inter-related drop down menus since that wouldn't kill calculation speeds as much

I was trying to have the "AIC", "AOC" and "BDC" configuration's listed instead of their description when dropping in data in column's D, E and F. When data is placed in Column's D, E, and F I was trying to think of what approach might be best to look at column's A and B to find the best fit match for each set of data in column's D, E and F to be indexed. I understand some of the data in column B is inconsistent, I think that is something I can improve on over time. Hope my explanation helps?

So what I am basically saying is to separate you're and f columns into things you would like to actually match.

So instead of something like 0-10 hz only matching 0-10 or hz you would have two separate columns.


You can do this with text to columns in the data tab, or by manually going through them all. Once that is done, this will become a solvable problem.