Can't figure out proper use of Index/Match to solve this problem

I think this is what you mean:

=INDEX($Y$2:$AH1027,MATCH(1,($B$2=$W$3:$W1027)*(B7=$X$2:$X1027),0),MATCH(C$5,Dashboard!$Y$2:$AH$2,0))

I didn't mention it because the OP already knows this - his original formula was array entered.

OMG!! YES YES YES!!

Thank you VERY much AliGW! This does EXACTLY what I needed!

Would you be able to explain what this does? I understand the following:

• INDEX($Y$2:$AH1027 = the index/table that the data will be pulled from
• MATCH(1,($B$2=$W$3:$W1027)*(B7=$X$2:$X1027),0) = Matches the date in B2 to a date in Column W, Matches the category in B7 to the category in Column X
• ,MATCH(C$5,Dashboard!$Y$2:$AH$2,0)) = Matches the name of the market in C5 across the headers in from Y2:AH2

What I can't understand is how the last MATCH works in relation to the greater equation. I'm guessing the first two matches are done first since they are in the first set of parenthesis and then it uses that data to match along the market in Y2:AH2 and gets the info from there. Is that correct?

I'll try to explain!

In an INDEX MATCH MATCH you have thee following:

INDEX - the array from which the result is pulled.
MATCH - the row match data.
MATCH - the column match data.

So, you have pretty much identified what each section does in your post.

AliGW,
I should be more clear. What I don't understand is the "MATCH(1,". Why do we put a 1 in there? Does that tell excel that the info contained within is the "first" lookup/check?

It's because there are two arrays in that section of the formula to identify the matching row. In each of the two arrays, there will be 0 and 1 (FALSE and TRUE). Where there is a 1 in common in the two arrays, it will find its match (1x1=1 - all others will be either 1x0=0 or 0x1=0).

Use the Evaluate Formula option on the Formulas ribbon to step through the formula to see how it works.

Awesome! Thank you for explaining that to me!!

No worries.

By the way, please stop all the unnecessary quoting. Thanks.

Hi Bullpen,

First of all, I pray that you and yours are well and coping with these difficult times.

I took a different approach, and used OFFSET/MATCH. I noticed that your Current Formula seemed to be giving the wrong answer - not picking up the correct week. Having said that, I see Ali got the same answers as you, but I don't understand why. I must be missing something.

The second thing I noticed, the data is consistent in that it always has the three components, so looking up Not Started, In Progress etc in the data was not necessary. It is always one, two or three cells removed from the first time the date is found.

This is the formula I came up with. It can be entered into C14 and copied down.
=OFFSET($X$2,MATCH($B$2,$W$3:$W$59,0)+LEFT(B7,1)-1,MATCH(C$12,$Y$2:$AT$2,0))

For D14 you need =OFFSET($X$2,MATCH($B$2,$W$3:$W$59,0)+LEFT(B7,1)-1,MATCH(C$12,$Y$2:$AT$2,0)) because you access a different date.


I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".