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.![Frown :( :(](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f641.png)
Thank you
p.s. Sorry for my grammar
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.
![Frown :( :(](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f641.png)
Thank you
p.s. Sorry for my grammar