PDA

View Full Version : [SOLVED] Index match, working with values up to .99 but not above 1



Jill
07-06-2017, 11:19 PM
Hi.

I am using office 2010. I'm new to coding and would appreciate help from any excel guru that is willing to help a newbie.

I am trying to debug a worksheet I've created. Its super complicated but appears to work with one exception, as far as i can tell, the index/match works from .005-.95. As soon as i reach 1.0, the macro does not return a value.

In the attached example, ThreeStone.xls, the issue is with Sheet1, column L. In the attached sheet you will see that L6 & L7 have a value of .4. This works as expected.
However, based on the formula in column L I would expect L4 to have a value of 1.75, but instead it gets #REF!

The formula is as follows
=INDEX(RBC!D2:D94,MATCH("Diamond_Round"&Sheet1!I4,E4&RBC!B2:RBC!B96,1))

The formula references the RBC worksheet which gives a range in which the value could fall. If it falls within that range it should return a value in the Carat column.

To give a bit of background info, this worksheet is automatically generated from another program. Essentially, I open a CAD file, process the CAD, pulling info and inputting that info into a Master excel sheet. This is the result. As you can see, there are a lot of empty rows. This is because each CAD file is different and could have anywhere from 1 row of info to 80 rows of info.

I'm also attaching a file called ThreeStoneWorking.xls. This is the same CAD except with a smaller stone (less than 1.0CT). In this example it works! See Sheet1 Column L. Like I said as far as i can tell, the macro works when Sheet1, I4 is less than 6.5 (mm) which is less than 1.0 CT. As soon as it is above, the macro fails.

Any ideas what Im doing wrong? Many thanks in advance.

Jill

mdmackillop
07-07-2017, 01:18 AM
I think the issue is that you are combining numbers into a string and then looking at an ascending series. In that case, Diamond Round7.72 > Diamond Round15.76 giving return value of 95 with an array size of 94.
Use Formulas/Evaluate to see how the values change.

You'll need to use formatted text values (omit space at : D94)
{=INDEX(RBC!D2: D94,MATCH("Diamond_Round" & TEXT(Sheet1!I4,"00.00"),E4&TEXT(RBC!B2:RBC!B96,"00.00"),1))}

Bob Phillips
07-07-2017, 08:55 AM
Why not just outsort the unwanted items?

=IF(E4="Diamond_Round",INDEX(RBC!D2:D94,MATCH(I4,RBC!B2:RBC!B96,1)),"")

Jill
07-07-2017, 12:33 PM
Wow guys! Thanks so much for the response. I tried Mdmackillops solution and couldnt quite get it to work. Not sure what I was doing wrong. Removed the space. Then I tried the formula supplied by xld and it appears to work. Ive tested it with about 20 designs and everything seems to jive. I really appreciate all of the help! I am always amazed at the generosity found on forums. I have learned a ton from reading forums and by the help of people like you! Much appreciated!

Jill