Index Match Wild Card doesn't work

[pleaserespond]

Hello! This is my first post, largely out of desperation to leave the office by 5!

I have a spreadsheet with a customer name along with other columns of data (as in Table 1). There is also a list of customers whose rows I want to delete from the spreadsheet (Table 2). I placed a column into Table 1 and inputted the formula below to try to display which rows need to be deleted. However, it isn't working. From other posts it seems I'm missing something important, but I don't know what exactly-a SEARCH function possibly? Thank you!

 

[gaz_chops]

Try using the wildcard in the formula

=index($H$4:$H$5,match($D4&"*",$G$4:$G$5,0))

In your example above I would use something like

=index($H$4:$H$5,match(Left($D4,5)&"*",$G$4:$G$5,0))

 

[circledchicken]

Hi and welcome to the forum,

Perhaps try something like this instead:

=LOOKUP(2, 1 / SEARCH(G$4:G$5, D4), H$4:H$5)

 

[pleaserespond]

I was hoping to avoid limiting the characters, as there are some text strings that start off very similarly. However, it was a success! Thank you, thank you thank you! I will still look for a better way for next month, but I can always double-check it manually today. Can't wait to enjoy the weather. Have a good day!

 

[pleaserespond]

circledchicken, thanks for the reply. what is the / for in your function? I am not familiar with either Lookup or Search, but I intend to learn them seeing my difficulties here.

 

[pleaserespond]

The issue with the limited string search is a little rougher than I thought. There are strings that are the same for the first 7 or so characters. But if I try to extend the character limit to 8+, then short words that should be deleted get overlooked. So I'm using a very short (4) string in the formula and just checking them all. Still faster than by eye alone. Thanks

 

[circledchicken]

You're welcome.

The '/' is a division operator.

Aladin Akyurek provides a detailed explanation of this type of formula construct here:
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html

 

[gaz_chops]

If it is mainly apostrophe's that cause a problem, you could strip them out, also you could amend the left function to find the 1st space therefore return the whole word to search for.

 

[gaz_chops]

If what i said above is true then try

=INDEX($H$4:$H$5,MATCH(IFERROR(SUBSTITUTE(LEFT(D4,FIND(" ",D4)-1),"'",""),SUBSTITUTE(D4,"'","")),$G$4:$G$5,0))

 

[pleaserespond]

Hello! Thank you all for your replies.

I wanted a complete solution, so I continued searching forum posts and luckily found a BRILLIANT formula in this thread (see Biz's Multisearch post)! I am so glad that I came to MrExcel!

http://www.mrexcel.com/forum/excel-questions/672766-search-multiple-words-cell.html#post3333650