excel - adding wildcard to look up formula? -

Look at the function I'm using the following formula as part of a multi-result. Many of these threads have been posted in one row below each other, so if I write Catering on the sheet 1 in cell K22 then my formula Matching word appears in column, produces name in sheet 2 on b and column a . In the formula to duplicate this lookup some times to get other values ​​from other columns. Similar matching results

For example, on my sheet 2 my columns look like:

  Name of the location Place number Amy Hotel London 1 Dave Hotel Manchester 2 Mike Catering Birmingham 3  

I have to use wild cards around my cell K22 in this formula to show that if I describe the type of hotel I Is like; I.e. hotels, hotels ... etc ... get results? ... I know that this index match can be used, but can I use them with the following code? At the moment it is an array formula, but I do not get any results when I try to do it like this. Tanks = IF (ISERROR (Index $ 7 = "*" & Sheet1 $ K $ 22 & "*", Line ($ B $ 1: $ B $ 7) !), Line (1: 1)), 1)), "", index (Sheet2 $! A $ 1: $ D $ 7, small (IF (Sheet2 $ B $ 1: $ B $ 7 = "*" & Amp; Sheet1 $ K $ 22 & "*", Line ($ B $ 1: $ B $ 7), Line (1: 1)), 1)) & amp; "-" and IF (Easter (index (sheet 2! $ A $ 1: $ D $ 7, small (if (sheet 2! $ B $ 1: $ B $ 7 = "*" and Sheet1! $ K $ 22 & amp; "*", line ($ B $ 1: $ B $ 7), line (1: 1)), 3)), "index" (Sheet2 $ A $ 1: $ D $ 7, Small (IF (Sheet2 $ B $ 1: $ B $ 7 = "*" and Sheet1! $ K $ 22 & amp; "*", ROW ($ B $ 1: $ B $ 7)), ROW (1 : 1)), 3)) & amp; "-" and IF (Easter (index (sheet 2! $ A $ 1: $ D $ 7, small (if (sheet 2! $ B $ 1: $ B $ 7 = "*" and Sheet1! $ K $ 22 & amp; "*", line ($ B $ 1: $ B $ 7), line (1: 1)), 4)), "index" (Sheet2 $ A $ 1: $ D $ 7, Small (IF (Sheet2 $ B $ 1: $ B $ 7 = "*" and Sheet1! $ K $ 22 & amp; "*", ROW ($ B $ 1: $ B $ 7)), ROW (1 : 1)), 4 <

P>

function (and in other contexts), you can not use them here. When you have = Using wildcard with direct comparison, i.e.

Sheet2! $ B $ 1: $ B $ 7 = "*" and Sheet1! $ K $ 22 & "*"

* Not a Wild Card , Is interpreted in the form of a literal star

replace all the above examples with

ISNUMBER (SEARCH (sheet1! $ K $ 22, sheet 2! $ B $ 1: $ B $ 7))