Index Match multiple criteria with wildcard

I am trying to come up with a formula that can capture the commission rate of a sales rep based on the name of a company, however in some cases the company name is not an exact match. As such, I need to figure out how to incorporate a wildcard that references text in another cell. I also am looking to incorporate the IfError formula to avoid #N/A in my results.

On a final note, I would like to do this without using the array function if possible.

Attached is my example. Thank you in advance.

Try this ...

=INDEX($K$5:$K$7, MATCH(1,INDEX(ISNUMBER(SEARCH($J$5:$J$7,B4))+0, 0), 0))

or:

=LOOKUP(2,1/SEARCH($J$5:$J$7,B4),$K$5:$K$7)

I also noticed the examples submitted did not include multiple criteria. What I also need is for the sales reps name to be included as one of the criteria. Attached is an updated example. As you can see, I was able to incorporate the iferror function but not the search or wildcard function in the string. Were getting closer!

Thanks in advance.

Based on the sample that you shared in post #6, try this adjustment to the suggestion from post #2:

C4 =IFERROR(LOOKUP(2,1/((SEARCH($J$4:$J$9,$B4))*($L$4:$L$9=C$3)),$K$4:$K$9),"")

Whoops, wrong thread. Deleted.

Thank you for your reply. I have two follow up questions. Is there a way to write this using Index Match instead of Lookup so I can move columns around at a later date? Also, I notice if I remove the word "Us" in cell B6 that the calculation will no longer give the desired result. On the other hand, if I remove the word "Us" in cell J8 the formula works perfectly. In other words, the wildcard/Search function only appears to go one direction.

Thanks in advance.

Try:

=IFERROR(IFERROR(INDEX($K$4:$K$9,MATCH(1,INDEX((SEARCH($J$4:$J$9,$B4)>0)*($L$4:$L$9=C$3),0),0)),
INDEX($K$4:$K$9,MATCH(1,INDEX((SEARCH($B4,$J$4:$J$9)>0)*($L$4:$L$9=C$3),0),0))),"")

Bingo! This works exactly how I envisioned. Thank you so much!

Another formula with the AGGREGATE function

Formula:

Please Login or Register  to view this content.

Thank you. Can you clarify what the numbers 15 and 6 represent in the string?

https://docs.microsoft.com/en-us/sql...s-transact-sql

Thank you for the link. It is a bit over my head so I will stick with the Index Match function.

Excellent formula! Doesn't even use cell formulas!

I have edited the sheet to use Named Ranges, I find this more useful and other users might as well.

FYI:

The 15 and the 6 are inputs to the aggregate formula, which has different functions, so these numbers just indicate the selection by the user, the value themselves are not related to the equation.

15 - SMALL
6 - Ignore Hidden Values

Thank you for clarifying. Your added input was very helpful!