Two-Way Lookup with wildcard

[jstriker]

I'm having difficulty finding a solution on the internet for this lookup. I am looking for a 2-way lookup with 2 vertical ranges and one horizontal range. And one vertical would need to be a wildcard lookup.

The lookup criteria for column A would need to be "wildcard" match from cell I2. I tried variations of XLOOKUP and INDEX MATCH but couldn't get to work. The combination of the 2-way and the use of wildcards makes it trivky, Any help would be appreciated.

 

[DanteAmor]

I did not put all the data, but with the following I could do tests.

Try this:

Dante Amor
	A	B	C	D	E	F	G	H	I
1									Criteria
2	Industry		Below 25%	25% to 50%	50% to 75%	Above 75%			Automotive & Industrial
3									Director Strong
4	Aerospace, Defense & Airlines, Automotive & Industrial	Director Strong	3%	4%	5%	6%			25% to 50%
5	Preformance Improvement, Digital	Director Core +		5%	6%
6									4%
7
8
9
Hoja5

Cell Formulas
Range	Formula
I6	I6	=SUMPRODUCT((ISNUMBER(SEARCH(I2,A3:A9)))*(B3:B9=I3)*(C2:F2=I4)*C3:F9)

Note XL2BB:​

For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[jstriker]

Thank you Dante!!! That works really well. I did try the XL2BB add-in but the security on my laptop is blocking it from working. I will work on getting that working going forward.