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

SamCha

New Member
Joined
Nov 23, 2020
Messages
30
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
It is an array formula, you must confirm it with Ctrl + Shift + Enter.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
Solution
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))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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
Back
Top