Wildcards

[Fishboy]

Hi all,

I am trying to find a way to incorporate wildcards into a number of formulas in Excel 2010 if possible.

Basically I have a sheet called "Raw Sales Data" which contains all sales of all items, and on another sheet called "Sales Ledger" I use various formulas to count how many sales there have been, how much they went for, what the max, min and average prices were etc etc.

Everything is working fine for items with entirely unique names, however there are a number of items in "Raw Sales Data" where the prefix of an item name is of standard format (e.g "Whispering Iron Band"), but there are a number of possible suffixes e.g. "Whispering Iron Band of the Fanatic", "Whispering Iron Band of the Deft" and so on).

I am looking to find a way for the following formulas to search for "Whispering Iron Band*" so that it counts all entries with this prefix as a single group of items.

These are the current formulas that I need to incorporate wildcards into (where $B12 needs the wildcard added):

=(INDEX(Data_MarketValue, MATCH($B12, Data_Item, 0))/10000)

{=(MAX((Sales_Item=$B12)*Sales_ItemValue)/10000)}

{=(MIN(IF(Sales_Item=$B12,Sales_ItemValue))/10000)}

{=IFERROR((AVERAGE(IF(Sales_Item=$B12,Sales_ItemValue))/10000),"0.00")}

=(SUMPRODUCT(((Sales_Item)=$B12)*(Sales_QuantitySold)*(Sales_ItemValue)))/10000

I am aware that not all Excel functions work properly when a wildcard is introduced, but I am hoping it is possible to doctor these to accommodate.

Does anyone have any ideas?

 

[Andrew Poulsom]

For MATCH you can add a wildcard, i.e. $B12&"*". Otherwise you need:

LEFT(Sales_Item,LEN($B12))=$B12

 

[Fishboy]

For MATCH you can add a wildcard, i.e. $B12&"*". Otherwise you need:

LEFT(Sales_Item,LEN($B12))=$B12

Hi Andrew, thanks for your prompt reply. The change to the MATCH formula was simple enough and easy to implement, but I am a little confused with the other answer.

How exactly do I plug LEFT(Sales_Item,LEN($B12))=$B12 into the existing formulas? Am I literally just replacing Sales_Item=$B12 as below?

{=(MAX((LEFT(Sales_Item,LEN($B12))=$B12)*Sales_ItemValue)/10000)}

 

[Fishboy]

Nevermind, I have worked it out. You suggestion works great! Thanks for your help!