Multiple way Lookup with wildcard & to return #N/A is criteria is not found

[Sean15]

@DanteAmor

Your solution here Two-Way Lookup with wildcard is great.
But if criteria is not in search range, formula returns 0%.
Can you post formula that return #N/A if criteria is not found in search range?

Regards,
Sean

 

[DanteAmor]

If you have the Filter function, try this:

=FILTER(INDEX(C3:F9,,MATCH(I4,C2:F2,0)),(B3:B9=I3)*(ISNUMBER(SEARCH(I2,A3:A9))))

 

[Sean15]

I don't have FILTER function but thanks for your help.
I just want to differentiate real 0% from 0% returned when criteria are not met.

 

[DanteAmor]

Try this array formula:

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%			digi
3									Director Core +
4	Airlines, Automotive & Industrial	Director Strong	3%	4%	5%	6%			25% to 50%
5	Preformance Improvement, Digital	Director Core +		5%	6%
6									0.05
7
8
9
Hoja5

Cell Formulas
Range	Formula
I6	I6	=IFERROR(INDEX(C3:F9,MAX((ISNUMBER(SEARCH(I2,A3:A9)))*(B3:B9=I3)*ROW(B3:B9)) - ROW(B3) +1,MATCH(I4,C2:F2,0)),"Not match")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sean15]

Thank you very much. Will test it over the weekend.