Including a search function in a match formula

**Weasel** · 12-15-2009, 08:03 PM

So I've got the following formula:

=IF(ISNUMBER(MATCH($A13 & C$12, INDEX('Men''s Dump'!$A$1:$A$4500 & 'Men''s Dump'!$C$1:$C$4500, 0), 0)), INDEX('Men''s Dump'!$A$1:$AQ$4500, MATCH($A13 & C$12, INDEX('Men''s Dump'!$A$1:$A$4500 & 'Men''s Dump'!$C$1:$C$4500, 0), 0), MATCH($A11, 'Men''s Dump'!$A$8:$AQ$8, 0)), 0)

I need to modify it so that instead of finding an exact match for cell A13 I instead want it to just consider it a match if it finds the characters in A13 someone inside a call value.

So as an example, if the value in cell A13 was Tom and that word was located some where in a cell in A1:A4500 in the Men''s Dump sheet then it would be considered a match. So if a Cell had a value of ="Mike Tom Hill" then it would be considered a match.

Any help would be greatly appreciated. Thanks

**teylyn** · 12-15-2009, 08:41 PM

maybe something like

=countif($A$1:$A$4500,"=*"&A13&"*")>0

This will produce TRUE if the value in A13 is found anywhere in the specified range. I don't want to touch your formula without a sample spreadsheet, though.

Unfortunate choice of sheet name, btw

hth

**Weasel** · 12-15-2009, 08:46 PM

Basically the formula is working so that it's looking to match cell A13, C12, and A11. When it matches all 3 in the Men's Dump sheet that it displays the value of that cooresponding cell. I'm trying to figure out how to alter the formula so that instead of matching the exact value of A13 it instead just trys to find word in A13 inside of a cell.

**teylyn** · 12-15-2009, 09:07 PM

Just replace your condition with my formula sans the =

You have your workbook in front of you and can test the formula. I don't.

**Weasel** · 12-15-2009, 09:21 PM

Are you saying change the formula to look as follows:

=IF(ISNUMBER(MATCH(COUNTIF($A$1:$A$4500,"=*"&A13&"*")>0 & C$12, INDEX('Men''s Dump'!$A$1:$A$4500 & 'Men''s Dump'!$C$1:$C$4500, 0), 0)), INDEX('Men''s Dump'!$A$1:$AQ$4500, MATCH(COUNTIF($A$1:$A$4500,"=*"&A13&"*")>0 & C$12, INDEX('Men''s Dump'!$A$1:$A$4500 & 'Men''s Dump'!$C$1:$C$4500, 0), 0), MATCH($A11, 'Men''s Dump'!$A$8:$AQ$8, 0)), 0)

**teylyn** · 12-15-2009, 09:30 PM

That's exactly what I do not want to answer without seeing the workbook.

**DonkeyOte** · 12-16-2009, 03:57 AM

As I see things a COUNTIF won't work here given the criteria are multiple (A & C)

In general terms try adding wildcards to your MATCH criteria, eg:

=IF(ISNUMBER(MATCH("*"&$A13&C$12&"*",INDEX('Men''s Dump'!$A$1:$A$4500&'Men''s Dump'!$C$1:$C$4500,0),0)),INDEX('Men''s Dump'!$A$1:$AQ$4500,MATCH("*"&$A13&C$12&"*",INDEX('Men''s Dump'!$A$1:$A$4500&'Men''s Dump'!$C$1:$C$4500,0),0),MATCH($A11,'Men''s Dump'!$A$8:$AQ$8,0)),0)