Index and match function with multiple criteria including the wild card search

[SamCha]

Hi All,

Please can you help me on this.

I have a workbook with Sheet 1 having all the reference data and Sheet 2 have the actual data file. I want to do a index match function on Sheet 1 where A18:A600 is the wild card text search column (searching the Template name from Sheet 2 Column 2).

In Sheet 1 column B18:B600 there are customer numbers.

I am trying to place an Index - Match function in column D18 where the cell will lookup for the wild card search cell A18 and match the customer number cell also B18 to give the Template name (range for the Template name is in Sheet 2 - Column B). The customer number range in Sheet 2 is in column A.

I was able to get the desired Template name by doing Index-match function using the wild search range/column but its giving me an incorrect result with the customer number. I have used the below formula with not much help and its giving me the error #VALUE!

=INDEX('Sheet 2'!B:B,MATCH("*"&'Sheet 1'!A18&"*"&'Sheet 1'!B18,'Sheet 2'!B:B&'Sheet 2'!A:A,0))

Truly appreciate if somebody can help me on this.

Thanks in advance.

 

[Phuoc]

It is an array formula, you must confirm it with Ctrl + Shift + Enter.

 

[SamCha]

It is an array formula, you must confirm it with Ctrl + Shift + Enter.

Thanks a lot. Not sure how did I miss that.

 

[SamCha]

Hi Phuoc,

Seems the formula didnt work according to what I was looking at

{=INDEX('Sheet 2'!B:B,MATCH("*"&'Sheet 1'!A18&"*"&'Sheet 1'!B18,'Sheet 2'!B:B&'Sheet 2'!A:A,0))}

The above formula is showing result in cell D18 even if there is no search text in cell A18. The cell D18 is picking the value matching cell B18 customer number. What is required from the formula is that cell D18 will only show its result when lookup value matches with both A18 and B18.

Thanks in advance.

 

[Phuoc]

Try this:

=IF(OR('Sheet 1'!A18="",'Sheet 1'!B18=""),"",INDEX('Sheet 2'!B:B,MATCH("*"&'Sheet 1'!A18&"*"&'Sheet 1'!B18,'Sheet 2'!B:B&'Sheet 2'!A:A,0)))

Ctrl+Shift+Enter.

 

[SamCha]

Try this:

=IF(OR('Sheet 1'!A18="",'Sheet 1'!B18=""),"",INDEX('Sheet 2'!B:B,MATCH("*"&'Sheet 1'!A18&"*"&'Sheet 1'!B18,'Sheet 2'!B:B&'Sheet 2'!A:A,0)))

Ctrl+Shift+Enter.

Hi Phuoc,

Tried the formula. It didnt work. Giving me a value 0.

 

[Phuoc]

Try again:

=INDEX('Sheet 2'!B:B,MATCH("*"&'Sheet 1'!A18&"*"&'Sheet 1'!B18,IF(('Sheet 2'!B:B<>"")*('Sheet 2'!A:A<>""),'Sheet 2'!B:B&'Sheet 2'!A:A,"|"),0))

 

[Phuoc]

Or try this:

=IF(OR('Sheet 1'!A18="",'Sheet 1'!B18=""),"",LOOKUP(2,1/SEARCH('Sheet 1'!A18,'Sheet 2'!B:B)/SEARCH('Sheet 1'!B18,'Sheet 2'!A:A),'Sheet 2'!B:B))

Just Enter.

 

[Phuoc]

If you want exact search, try this formula:

=IF(OR('Sheet 1'!A18="",'Sheet 1'!B18=""),"",LOOKUP(2,1/('Sheet 1'!A18='Sheet 2'!B:B)/('Sheet 1'!B18='Sheet 2'!A:A),'Sheet 2'!B:B))

 

[SamCha]

Or try this:

=IF(OR('Sheet 1'!A18="",'Sheet 1'!B18=""),"",LOOKUP(2,1/SEARCH('Sheet 1'!A18,'Sheet 2'!B:B)/SEARCH('Sheet 1'!B18,'Sheet 2'!A:A),'Sheet 2'!B:B))

Just Enter.

Thanks a lot Phuoc. This one worked perfect!! The last formula for exact search did not work because the same customer number have more than one Template Names linked to it. The result for the last formula is showing #NA.