PDA

View Full Version : Wildcard with match index



Jill
07-25-2017, 10:34 AM
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
07-25-2017, 02:05 PM
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
07-25-2017, 06:35 PM
Thanks Paul that worked!

Jill
09-29-2017, 08:16 AM
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
09-29-2017, 10:43 AM
They all look like FALSE or #VALUE to me after I run the macro.

Jill
09-29-2017, 03:04 PM
I'm attaching another version.

Bob Phillips
09-30-2017, 12:56 AM
Looks the same to me.

mdmackillop
09-30-2017, 05:07 AM
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
10-02-2017, 07:15 AM
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
10-02-2017, 09:34 AM
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
10-02-2017, 12:50 PM
Rookie mistake.

Thanks for the help.

Blushing,
Jill