IndexMatch w/3 (or more) criteria

I have data on one worksheet which needs to populate specific cells on another. I am having trouble with the index match functions.

The data is organized by fiscal year by work week, there can be several flow numbers used during the same work week which utilze different part numbers (for products)

I have used the concantenate function to index work weeks and extracted one bit of data using vlookup. Here is are additional details;

1- Cells B4:X7 are actual data (shaded green) Example purpose

2- Cells B8:X15 need to be populated from the data in RangeName INPUT(M25:Q32), cells shaded yellow and are entered manually.

3- Named Ranges; CONCAN M25:M32, FLOW I8:I15 is populated with a lookup function, FLOW2 O25:O32, FLOW3 W25:W680 (a complete list of flows), PARTNO1 L3:X3, PARTNO2 P25:T32, WWINDEX C:8:C15

I have lefts some of my attemps in the cells L8:X15.

The files are attached.

This one has really got me frustrated.

Any help would be appreciated.

It is not very clear to me exactly what you want to return.

If I take the formula you had in cell N8, and replace the last Match with the number 1, it returns the Cost Point (if that is what you wanted?). The third argument in INDEX (the way you are using it) is the Area number ... which only makes sense when the Range is discontinuous. Otherwise, there is only one Area.

The other way I got to the same answer is with this formula:

Please Login or Register  to view this content.

But, still not sure that is what you wanted.

I am looking to extract the Count (CT) from the Input data.

For example from the input table, if the value in M25 is FY2007WW40-1, the FLOW2 value is 105 (in cell O25), which means the PN is 4156 (in P25) and the count is 50 (in Q25).

The formula in Cell S8 would return the value 50. Every thing else would be zero (or blank). The correct results are shown in the green shaded area.

Hope this calarifies.

Sorry for the confusion.

No wonder I did not get it the first time ... my brain is hurting! OK, see if this gives you what you need (formula for cell U5)...

Please Login or Register  to view this content.

If so, I will see what I can do to get rid of the reference to column Q and replace it with a Name you already have (but I think it will involve an OFFSET).

I was afraid you were going to say something like that.

I can explain the logic and why it does not work. Then you can try to explain it to me one more time, because the goal has not sunk into my brain quite yet.

Let's start with the Index/Match inside the SUMPRODUCT.
INDEX(PARTNO2,MATCH($I5,FLOW2,0)

This is bringing back the part number that matches the flow/MSL. And this is what is not working. In cell L8 (or any column, for row 8), this Index/Match set will return part number 4156. Which does not match part number 3549. Which makes me wonder ... what SHOULD go in cell L8?

Once we have the part number (for now, let's call it 4156), then
PARTNO2=INDEX(PARTNO2,MATCH($I8,FLOW2,0)))
will compare each member of the ARRAY named PARTNO2 and return an ARRAY of TRUE and FALSE ... it will be TRUE for each member of PARTNO2 that matches 4156 and FALSE for each member of PARTNO2 that does not match 4156. If you multiply TRUE by 1 (or by -1 twice, which is what -- does), you get 1. If you multiply FALSE by 1, you get 0. So,
--(PARTNO2=INDEX(PARTNO2,MATCH($I8,FLOW2,0)))
returns an array of 1's and 0's.

Similarly,
--(CONCAN=$C8)
returns an array of 1's and 0's; it returns 1 when a member of CONCAN matches the entry in cell C8 and it returns 0 when a member of CONCAN does not match the entry in cell C8.

The formula
ROW(FLOW2))
returns an array of row numbers for each member of FLOW2; in other words, it returns this array:
{25;26;27;28;29;30;31;32}

SUMPRODUCT multiplies these 3 arrays. If either CONCAN or PARTNO2 does not match the criteria, it returns a 0 (zero times anything is zero). If both CONCAN and PARTNO2 match the criteria, multiplying these two together gives 1 (1 times 1), when multiplied by the Row numbers, SUMPRODUCT will return the one row number where both criteria are met. As long as there is only one such row number. If more than one row meets both criteria, we have a problem, because now SUMPRODUCT will return the sum of the row numbers, which is meaningless.

In this case, SUMPRODUCT is returning 25, telling us that what we are looking for , it thinks it found on row 25.

Once we have the row number (assuming we do!), then using the single vector form of INDEX for the entire column Q will return the cell value of Q25.

Unfortunately, as brilliant as all of that is, it is not quite what you wanted. So, please explain again and let's see if I can understand it this time.

Okay, let me clarify.

The values in B4:X7 are for example only (shaded green) and manually entered. This is how the data should look if the formulas in the rows below work correctly.

The numbers in cells I8:I15 (Flow) is obtained from O25:O32 (Flow2) via a lookup

In cell S8 the value needs to be 50 and everything else in the row 8 blank or 0. I have been trying to use index/match a result of M25:Q32 (INPUT) comparing O25 (value 105) to I8 (value 105) and S3 (value 4156) to P25 (value 4156) and returning the value 50 from cell Q25 (the CT column (Q25:Q32))

What has been wrapping me around the axle is the same part number (i.e. 4101) can be associated with the same Flow (i.e. 206 & 216).

I have tried concatenating columns but have gotten messed up.

Thanks for taking the time on this.

OK, this should work ...

Please Login or Register  to view this content.

It is the same as before, except with one more condition that must be met. Namely, not only must the PARTNO2 equal the result of matching column $I with FLOW2, but it must ALSO match the value in row $3.

If you understood the gibberish I posted earlier, explaining the SUMPRODUCT and INDEX/MATCHes, then you will understand this as well. If not, let me know and I will try to find a better explanation (I know I have a complete explanation somewhere).

I have attached a file to help our understanding. The formatt is the way the data is layed out in real life.

There are two sheets in the workbook, ‘Populate’ and ‘inputdata’. On the Populate sheet rows 4 – 8 are for example. Rows 9 – 16 is actual data populated by the latest formula.

Cells L9:X16 show the results of the latest formula, correct results is in blue text with green background. Incorrect results are black text with pink background. Cells L9:X12 have correct results and incorrect results.

Cells L13:X16 appear to be all correct, which mystifies the heck out of me because the formula looks the same. Could it be caused by the range I supplied for the SUMPRODUCT function? (Just a guess, I obtained flakey results before moving the input range to another sheet as well).

Thanks again for your time and effort. I think we are close.

The only attachment I see is the original. It has only one sheet. Try again?

again titled IndexMatchProblem_v2.

OK. I have the file and think I have the answer. First ... here's a trick to use when debugging cell formulas ... you can select any portion of the formula (as long as it is itself a valid formula), press F9 and get the intermediate results.

So, for example, take cell L9. If you select the SUMPRODUCT part of this and press F9, you end up with: =INDEX(inputdata!$H:$H,0)

In other words, the SUMPRODUCT correctly tells us that there was no match. Now, I would think that would result in an error, wouldn't you? But, for some reason it instead gives us 50, which is dead wrong.

To fix this problem, I suggest changing the formula slightly. If the SUMPRODUCT gives us 0, we should trap that error and fix it before it gets to the INDEX funtion. I like this solution:

Please Login or Register  to view this content.

It seems to give the correct answers in all the cells.

MSP77079,
the solution works in the sample data. Thank you sooo much! It will take a day or two to implement in the real world, if I have issues I'll let you know.

Again, Thank You for your time and tutoring!

Hi Tim,

Sorry for delay in responding. Got tied up yesterday.

The problem turns out to be very simple; but, something I only know to look for because it bites me so often. When using using arrays in formulas (SUMPRODUCT is always referring to an array), it is necessary that each array be exactly the same length.

In the workbook uploaded, the range Named CONCAN was defined slightly differently from the other ranges used. For example:

Please Login or Register  to view this content.

You can change the definition for CONCAN using Insert >> Name >> Define. Change $D$22 to $D$23 and everything will work again.

I would have never spotted that!

You are awesome! I will get it put into the master/monstor spread sheet.

Thanks again!

Hello MSP77079,
You had helped me with this issue in March and it had worked quite well for until recently. And I am hoping you (or someone) can help me again.

Now the formula is not finding the the device and amount consistantly. As a refresher;

The Range L3:X441 is populated by the formula
=INDEX(FADataEntryLookUpTable!$H:$H,MAX(1,SUMPRODUCT(--(CONCAN=$C3),--(PARTNO2=L$2), --(PARTNO2=INDEX(PARTNO2,MATCH($I3,FLOW2,0))),ROW(FLOW2))))

The Columns A - K are a combination of cut and pastes and vLookups. In column K if there is a zero (0) that means the above formula didn't populate the cells in the range L3:X441 and you will see 'other' in column L. The first occurance of this is in row 4 (index row1062). There are others which are highlighted light yellow. (I have deleted rows to meet file size requirements)

If you follow the formula you wrote you will see that the amount 50 should be in cell U4

What I can't figure out is why it finds the item in Row 2 (L2:X2) sometimes but not others.

Again, Thanks for your help
Tim

Sorry for not responding Tim, I've been tied up. Well, not literally.

I can tell you why it is not working. What I cannot tell you is why it should ever work.

Let's just look at cell U4. If you go to the formula bar, highlight the part of the formula that says:
MATCH($I4,FLOW2,0)
and then press F9, you will see that this part of the formula is returning the value 82. In other words, we are looking up "401" in column F, and the first occurance of it is in row 83.

Press the Esc key to avoid losing the formula.

So far, so good? Next, highlight the part of the formula that says:
INDEX(PARTNO2,MATCH($I4,FLOW2,0))
then press F9, you will see that this part of the formula is returning the value 4101.

Hope that's what you wanted.

Now we are at the part of this formula that I'm not sure I ever understood. We are trying to find a row where the PARTNO2 is 3552 (the value in cell U2)and at the same time the PARTNO2 is 4101 (what the first Index/Match gave us). I don't see how that is possible. Maybe I understood this a few months ago, but too many weekends in between.

I'm not sure this is helpful at all. Hopefully it's enough to get you started.