Wildcard with match index

[Jill]

Hi All,

{=IF(Y12 = "Diamond_Round",INDEX(F11:F88,MATCH(W12,B11:B88,1)),"")}

I am using the formula above successfully.

However I want to find any of the following text options in the place of Diamond_Round.
"Diamond_Round"
"Diamond_Oval"
"Diamond_Princess"
"Diamond_Marquise"
etc.

I've read about wildcards and have tried the following with no success.

{=IF(Y12 = "Diamond_*",INDEX(F11:F88,MATCH(W12,B11:B88,1)),"")}
{=IF(Y12 = "*Diamond_*",INDEX(F11:F88,MATCH(W12,B11:B88,1)),"")}
{=IF(Y12 = "Diamond_????????",INDEX(F11:F88,MATCH(W12,B11:B88,1)),"")}


I've attached a sample. The formula in question is in AA12.
Thanks in advance to anyone who has suggestions.

Jill

[Paul_Hossler]

It's had to test since all of the data seems to link to a file on Jill's Desktop, but try the LEFT() below

"=IF('C:\Users\Jill\Desktop\Dataport\SampleData\[Solitaire.3dm.xls]Sheet1'!D3="Gem 01", 'C:\Users\Jill\Desktop\Dataport\SampleData\[Solitaire.3dm.xls]Sheet1'!E3)"

=IF(LEFT(Y12,7)="Diamond",INDEX(F11:F88,MATCH(W12,B11:B88,1)),"")

[Jill]

Thanks Paul that worked!

[Jill]

I'm attaching a sheet where I am using this formula but not getting the expected results. The sheet is quite complex (I'm new to excel scripting so please excuse my Frankenstein approach to the solution).

To use the sheet please follow these steps.
1. First go to developer tab and run the macro "Sheet1.Gem"
2. View the tab titled "Scratch"

Note the result in AO = .005 The results should be .006
I'm not exactly sure whats happening. Any ideas? Thanks in advance to anyone who takes a look.

~Jill

[Bob Phillips]

They all look like FALSE or #VALUE to me after I run the macro.

[Jill]

I'm attaching another version.

[Bob Phillips]

Looks the same to me.

[mdmackillop]

I think you need to introduce data that you know will produce results and work through to the breakdown. There are so many False results, it's hard to follow what is going on

[Jill]

The attached sheet should have data that shows the error.

I opened the file from this thread on another computer. I had to do the following;

1. enable editing in excel
2. go to the developer tab and choose Macro Security (under Code). Then choose "Enable all macros".
3. Click Macro and choose Sheet1.Gem, Run

The unexpected results are the worksheet titled "Scratch" in AO 14. The value in the sheet is .005. I would expect it to be .006. It should have done an index match for a 1.1x1.1x.68mm and located the .006 in T15.

[mdmackillop]

Set your Match parameter to 0 rather than 1 for an exact match
If you use the Formulas/Evaluate Formulas tool you will see that your formula returns a Match value of 21

[Jill]

Rookie mistake.

Thanks for the help.

Blushing,
Jill