INDEX + MATCH with multiple criteria and spreadsheets

[Oggy385]

Hi guys,

I'm in a pickle for a few days now. My problem just wont go away and I've wasted few days on trying to solve it. I'm ready for some help since I hit dead end.

Let me be as simple as I can.

Sheet 1
[TABLE="width: 343"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]CUSTOMER[/TD]
[TD]RATE 1[/TD]
[TD]RATE 2[/TD]
[TD]RATE 3[/TD]
[TD]RATE 4[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="width: 179"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]CUSTOMER[/TD]
[TD]RATE[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I need values in Sheet 1 RATE rows to return (any) Text value if they match RATE number and Customer from both sheets

It would look like this
[TABLE="width: 343"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]CUSTOMER[/TD]
[TD]RATE 1[/TD]
[TD]RATE 2[/TD]
[TD]RATE 3[/TD]
[TD]RATE 4[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

Every time i try to use Sheet2!A:A or anything else from Sheet2 as criteria it would return N/A . Tried different formats and approaches but as soon as I enlisted Sheet2 it would give an error.
It doesn't matter if Sheet 1 RATE 1 will be renamed to 1 so I can use it in formula as criteria (e.g. B1) or if I used "1" as criteria in formula directly still N/a.

Things I tried was VLOOKUP, INDEX,IFERROR;MATCH,VALUE;TEXT and the simplest solution would be a plain INDEX MATCH formula with multiple criteria but it wont work.
Something in MATCH(1,(Cell Range=Cell)*(Cell Range2=Cell2),0) doesn't seem to work. Single criteria like Customer does'nt work untill I put data from sheet2 into sheet1.

Maybe I'm doing my calculations wrong but I've read like 150 posts and everything is returning me back to the begining.

Please help.

Thank you

p.s. Sorry for my grammar

 

[svendiamond]

First, change "RATE 1" and "RATE 2" etc to simply "1" and "2" get rid of the word RATE. Then you could use this formula in cell B2 on Sheet1:

=IF(ISNUMBER(MATCH($A2&B$1,Sheet2!$A$1:$A$10&Sheet2!$B$1:$B$10,0)),"Yes","")

It's an array formula so make sure you press CTRL+SHIFT+ENTER, not just ENTER. Then you can fill down and to the right.

 

[svendiamond]

Additionally, here is a non-array solution (in cell B2, and fill down and to the right):

=IF(SUMPRODUCT(--(Sheet2!$A$1:$A$10=$A2),--(Sheet2!$B$1:$B$10=B$1)),"Yes","")

 

[Oggy385]

Additionally, here is a non-array solution (in cell B2, and fill down and to the right):

=IF(SUMPRODUCT(--(Sheet2!$A$1:$A$10=$A2),--(Sheet2!$B$1:$B$10=B$1)),"Yes","")

First one didn't work. Already tried it, but the second one worked like a charm.
Havent tried IF SUMPRODUCT approach. Can you explain the formula a bit more?

 

[svendiamond]

On the first one, did you use CTRL+SHIFT+ENTER? You have to enter it like that (not just ENTER) in order for it to work.

The SUMPRODUCT function can return an array or set of arrays and compare them. The two dashes "--" turn "TRUE/FALSE" into 1s and 0s. So basically the function is creating two arrays. One array is of 0s and 1s based on whether the values in Sheet2!$A$1:$A$10 equal $A2 ("Customer A") so that is literally the array: "1,0,0,1,1"... then the second part is making an array based on whether the values in Sheet2!$B$1:$B$10 equal B$1 (the number "1" in this case) so that is literally the array: "0,1,0,0,0". Then SUMPRODUCT just adds together the arrays where both statements are true. In this case (cell B2) the result is zero.

The IF statement just returns a "Yes" if the SUMPRODUCT has any value besides zero.

 

[Oggy385]

Haven't seen double dash in a formula anywhere else. That explains it.

The first one works to, I've misplaced the wrong cell as criteria. The first input gave me no results at all so I thought it was wrong :S
Didn't knew CTRL SHIFT ENTER changes it that drastically.
Anyway thank you a lot