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

SamCha

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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!

Excel Formula:
=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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
314
It is an array formula, you must confirm it with Ctrl + Shift + Enter.
 

SamCha

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Hi Phuoc,

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

Excel Formula:
{=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

Active Member
Joined
Apr 29, 2016
Messages
314

ADVERTISEMENT

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

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Apr 29, 2016
Messages
314

ADVERTISEMENT

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

Active Member
Joined
Apr 29, 2016
Messages
314
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.
 
Solution

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
314
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

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top