"Very Basic Index/Match Problem"

[Radridz3]

Hello everyone,

This is officially my first thread on this site and want to thank the admins and mods right off the bat cause most of my sheet questions have always been answered with the vast amount of faq and threads on this site.
I have been working on this really in depth file with about 8 current sheets interacting with each other in reference to pulling data from other softwares and text and xml files from all over. I however cannot post it due to its sensitive nature.
So I'm working on this Match and Index area on one of the sheets and to me the formula and cells looks clean but for some reason its returning the value from one cell above even though its just set to ,0 as to equal the read cells value...
i went and tested a couple things and even went to help and looked into the basic example formula for INDEX /MATCH. It looks good to me but like I said it doesn't work. So what I did was setup a sample file with that very basic example and my formula for the section in question. I only really need to figure out why its not reading the qty... in the cell next to the matching value .... Thanks in advance and once again thanks for the sweet excel website... It's greatly appreciated..

J.HessForumSample1.xlsx

[FDibbins]

Your ranges do not match...
=INDEX(A2:B5,MATCH(A2,A3:A5,0),2)

If you change the INDEX range to the same as the MATCH range, it works.

However, I would put your search criteria in it's own cell

[shukla.ankur281190]

It just because you have taken index array A2:B5 and lookup table A3:A5 in match. Now according to match function 1111 is in 3 row ( retrieve fro range) and in Index array if you will extract it {1111,38;2,25;3,47;1111,32} "semi-comma means next row and comma means column in array argument". Now see the third semi-comma and which is after 47 as you mentioned column should be 2 then it return you 47.

If you want to retrieve 32 just need to put A7=INDEX(A3:B5,MATCH(A2,A3:A5,0),2)

You have to always in your mind that Index array and match lookup table will start and end with same cell references.

Hope its clear now.

[cbatrody]

Hi,

There are multiple matches for 1111 in column B. INDEX MATCH functions will only pick up the first match. Also, the data range mentioned in your formula for INDEX & MATCH do not match.

=INDEX(A2:B5,MATCH(A2,A2:A5,0),2)

If you have to lookup values for multiple matches you would need an array formula.

Try the following in A7, this will return the second match for 1111:

{=INDEX(B$1:B$5,SMALL(IF(A$1:A$5=A2,ROW(A$1:A$5)),ROW(A2)))}

array entered, needs to be confirmed by pressing CTRL+SHIFT+ENTER

[samba_ravi]

=LOOKUP(2,1/($A$2:$A$5=$A$2),$B$2:$B$5)
Try this formula
in your formula

INDEX(A2:B5,MATCH(A2,A3:A5,0),2) in this case MATCH(A2,A3:A5,0) returns 3
so = Index(a2:b5,3,2) returns b4, that is 47
if you change the your formula as =INDEX(A3:B5,MATCH(A2,A3:A5,0),2) It will give correct result
Try
=INDEX(A3:B5,MATCH(A2,A3:A5,0),2)

Please Login or Register  to view this content.

[Radridz3]

Thanks for the quick responses. Shulka and NFsales solved it. Changing the index range to take out A2 and starting at A3 worked perfectly. In reference to the other two posts> You cannot have a match and index range being the same it wont return the cell data needed. For example if the index range and match range were the same in this example it won't return the quantities in column B. (FDibbens) To cbatrody if your match range starts at the same cell as the value your trying to match you will never look down the list and match anything it will always return the A2 value. Thats why the match started at A3....

I still don't know if I'm going to be able to totally setup the section in my project with this method due to the amounts of areas that need to be read. I believe I have to find another way.. I will post my sheet I'm working on from the project and then attempt to explain all the variables and conditions that need to be true or change and or process a formula of some sort...

Also if you goto Help and type in match multiple. You'll find the formula in the basic example looks for pears and match's that text. All I did was change that text to A2 and then it didn't work. That's how I got stumped. I'm still stightly confused as to why it doesn't react the same way but I have a dead line to meet and am just moving on.....

Thanks for the input everyone I really appreciate it....

[Radridz3]

Take a look at sheet 2 in this example. I thought index and match would be how to solve this but there would be some many . I am pretty sure I'm not thinking outside of the box enough to achieve this setup the easiest way. Whatever ideas you may have please. Thanks for the help everyone...ForumSample2.xlsx

[FDibbins]

You have to always in your mind that Index array and match lookup table will start and end with same cell references.

Not necessarily. I have seen instances where 1 range is offset from the other. However, both ranges MUST be the same size

[Radridz3]

Please take a look at my last attachment. Thx

[Radridz3]

FDibbins how do I get my second example file in the post above solved by paying someone? And how much does it usually cost? I know it cost money to post in the commercial side but how do I determine what to pay someone for it?? I definitely need some help with it cause everything I keep doing ends up with some type of flaw after the fact. The Index and Match thing worked out for me and I learned a great deal on that question for sure but really need help with the 2nd Example File..
Thanks...

[FDibbins]

Sorry for the delay in replying, sometimes, when the forum is busy, threads get pushed down the list fairly quickly.

I will take a look at your file and get back to you

[FlameRetired]

I am not sure what you want returned or where the returns are to be. What if we start in steps?

I changed the values in B2:B3 to get some matches. Formulas in E6 and E12 are

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Does this produce the expected results so far?

Row\Col	A	B	C	D	E	F
1	BOX	OPEN		DEPTH
2	1-5L	7.5		14
3	6-10L	10		16
4	STAN./REDUCED/UNDERM./PULLOUT
5	QTY	FRONT HT.		S/R/U/P
6	3	7.5		S	3	In E6 =SUMIF($B$6:$B$10,$B$2,$A$6:$A$10)
7	2	10		S
8	1	10		R
9
10
11
12	3	7.5		S	5	In E12 =SUMIF($B$12:$B$16,$B$3,$A$12:$A$16)
13	2	7.5		R
14	2	10		S
15	3	10		R
16
17
18

[Radridz3]

Thanks for the response....

First let me give some insight as to what this section in this sheet accomplishes. This section measures drawer Fronts for cabinets. Basically in my other sheets the information or answer obtained in other sheets auto fills a form for the shop to produce and so on. Manufacturing Terms. A Traveler and or Cut list...

A2 and A3 are setup for two separate banks of drawers. This will expand once I have a clean working sheet but for now the least it can be is two . To be able to show what I'm trying to accomplish.
A2 and A3 are for the measurement of the shelf. As we call Opening...
D2 and D3 depth is irrelevant to any calculation we do in this section. This is used for Drawer Box Calculations only....
Also disregard the Standard / Pullout / Undermount Letters indicated in Red. This is also only for calculating Drawer Box's.

First and for most. The calculations start with reading the opening A2 and A3. If they match then all matching drawer front sizes in both quantity banks need to be added together for a total quantity of matching height drawer fronts.
IF the A2 and A3 dont match. Say on is 24"W and the other is 30"W then each quantity area adds up the matching drawer fronts in its own section.

So start with them not matching. A2 corresponds to B6-B11 in your photo. And A3 corresponds to B12-B18...
On average a job (Large Job has 1-8 different Drawer front heights so having it match a legend isn't going to work. And it has to read all lines in its respective area for match's of the same height.

I originally tried this with index match but once I got to the 3rd or 4th selection area the array got to be to much to keep track of. So I gutted it and now I'm here...

If this still seems a bit confusing I can provide you my email and send you a couple photos and word documents of a cutlist we currently use and a order form for Doors and Drawer Fronts. The Sheets I'm working
on allow us to only have to enter data once and all forms there on out are Auto-Filled.
I have the Cutlist , Parts Order , Sub Contractor Order , Installer Notes , and a couple other Forms already linked to my Workbook and pulling Data to and from for multiple things. Honestly this is the only part I haven't solved. Drawer Box calculations and sizes turned out great but thats all basic conditional formatting and basic commands...

I am willing to compensate for a working formula. Like I said this little hang up is the only thing keeping it in my office and not implemented...

Thanks in advance...

[FlameRetired]

At the moment I cannot visualize the 3 or 4 selection areas. I suggest we take this in steps to see if we are headed in the right direction.

I have modified the above formulas in E6 and E12. They are:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I have changed the values in B2:B3 to match so that these formulas will sum Quantity in both sections if they match. They still return separate values if they do not match.

I have attached the workbook this time so you can see everything in context.

Are we going the right way?

PS. Compensation will not be necessary. We're volunteers. We work for free. Thanks for the offer though. It is more than enough.

[Radridz3]

Alright let me try and explain this again but this time I'm only going to tell you the first thing. The opening which is the cell you are using to match will never match the drawer front height. The setup you have does not and will not ever find quanity's of matching drawer front heights....... Its two different measurements.

Step one: Does B2 and B3 equal the same value Yes or No.
IF YES then find matching Drawer front sizes in both sections. (B6-B10 is Section 1 & B12-B16 is section 2) Find matching Drawer Front heights in both sections..
IF NO then only find matching Drawer Front Heights in there respective section.

So B2 is the opening shelf size number for section 1 (B6-B10)
And B3 is the opening shelf size number for section 2 (B12-B16)

The opening number is irrelevant to the numbers your matching. Your matching Drawer Front Heights in there sections and returning the combined quantity of the match's..


So here is an example if B2 and B3 don't match values....
B2 = 24"
B3 = 30"
Then we are only going to find drawer front height matchs in there own sections. Not combined.
So Section 1
B6 = 7.5 "Does B6 match any other cells in the section thru B10? If it finds matchs (s) I repeat Match's then add the quantity of the matchs in that section.

Same thing in section 2 B12-B16... Find match's

Now if B2 and B3 values match each other then combine B6-B10 and B12-B16 and find all the Match's in both sections and return the quantities of the matchs..

Hope this help as I really don't know how to explain it a 3rd way. :-) But will do my best.....

[FlameRetired]

OK. I now understand there is no connection to the actual drawer heights. What is relevant is whether they (B2 and B3) are identical values or not.

I seem to be suffering "disconnects" in understanding the logic flow after that. You've mentioned only the 7.5 in both sections. Where does 10 come into play?

Can you supply some hard numbers to the expected outcomes in each instance you anticipate? I.e. if B2 is 24 and B3 30 do you expect the sum of Quantity in the first section to be 3 and 5 in the second section? If B2 and B3 are identical then do you expect the combined sum of Quantity to be 8? Or do you expect else?

[Radridz3]

The 7.5 and 10 are not hard numbers they will change to any random number. The 7.5 and 10 are standard examples of drawer front heights. It could be 5.38 or 7.64 or any numerical value for any setup of drawer fronts we need to order or make to accommodate the custom cabinet for the customer. When I get some free time later I will explain the flow again and include some pictures and other data that will help you understand what is going on here.... I know we can accomplish this I just don't know enough about match index if then and or ... It seems....

[FlameRetired]

The 7.5 and 10 are not hard numbers they will change to any random number. The 7.5 and 10 are standard examples of drawer front heights. It could be 5.38 or 7.64 or any numerical value for any setup of drawer fronts we need to order or make to accommodate the custom cabinet for the customer. ...........

If I understand the mission more clearly now, it would appear that MATCH will not do what you desire. MATCH matches the first occurrence it finds and that's all. To identify and use multiple matches (and then sum up the target Quantities) will require something like SUMIF(S) or SUMPRODUCT.

If I may be permitted to suggest a different approach here ... an approach that will help me to "back-engineer" my own understanding it would be this.
Suppose B2 and B3 are not the same. What would you expect the sums (the actual numbers) to be in each section in your upload example?

[Radridz3]

I will post this evening. And yes Sum Product or Sum If is gonna be the key. I have a few different ways I can explain this. I just need to upload some stuff to you....Drawings and what not to break it down. Its very simple once you see how we do it by hand. The goal of this project is to get rid of hand written or typed pages for different forms that have the same things on them. Its a waste of time and once this is up and running it will save me so much time when processing and writing code for the jobs.... I'm a cnc programmer by trade but that includes about 5 other job titles as well. I'm basically the guy that makes the designers drawings come to life in hand... I will get on this later tonight. Thanks for helping me out here.....

[Radridz3]

I posted a PDF of a couple example drawer banks. This will show you how the drawer fronts are calculated. Its all about the fronts. You can see that the left and middle unit are the same width but the banks are different depths. So basically during construction the box are different depth but the fronts are the same because the width is the same. I put counts for the fronts in each individual drawer bank and also put a total count to the right of them. You can see the Bank #1 on the left has 3x 7.5"H Fronts and so does Bank#2 in the center. So the total for that is 6x because they are the same sizes even thought the banks aren't the same technically speaking....DRW JEREMY FORUM HELP 10-13-15.pdf

[Radridz3]

Flame Retired here is another example of the same file with me trying to break it down to you. you can see in the 2 examples of the quantity totals.
If you use this and the pdf drawing I included in the last post you should be able to understand the variables... Thanks for your help so far...TEST 2 Handwritten Parts List.xlsx

[FlameRetired]

Radridz3 I must confess that I grow more confused with each posting.

This is beginning to shape up as more of a project appropriate for the Commercial Services section. Now this is important. If you decide you would like to have this thread moved to CS please do not do this yourself. Have a Moderator do it for you. If you do it yourself it I believe it results in a double-post ... something that is frowned upon in the Forum.

Regards,
Dave

[FDibbins]

FT, when a member opts to post their thread in CS, we just close this 1, and they continue there

[Radridz3]

Thanks guys for taking a look. Have a good one......You can mark this answered or delete it.....

[FlameRetired]

FT, when a member opts to post their thread in CS, we just close this 1, and they continue there

So no need to contact a Moderator?

[Radridz3]

Flame Retired Please take a look at this type of example file and see if you understand what I'm trying to achieve. This is just an all around example and doesn't have anything to do with my real world scenario.

No if's and or's then's....
Just a basic match and count game. Except you can just find the first match in a column you have to find all the matching values in the column and then add the value from the cell next to it in total.
I put example values and even answered the questions in a comment to show you what is going on.

I am really hoping that some formula can be made to accomplish this. I mean there has to be somewhere in the world someone is matching values and counting totals from different columns...
I know it cant be just plain match index because I would have to build an array to read every cell like five different ways to sunday and would look like jarvis from ironman when I was done just to accomplish something I do on a daily bases by hand. Every single day...Forum Sample 3.xlsx

[FDibbins]

So no need to contact a Moderator?

You can contact a Mod so they can close the thread

[FlameRetired]

@ FDibbins

Thanks.

[Radridz3]

Did you have a chance to look at that sample?