How to use sumproduct function or ......

[jamshoot]

Hi all

I trying to do a cross check data based on criteria is Sheet1. Sheet2 contained my raw database data. How to compile an excel function using sumproduct to do it. The criterias in Sheet1 is A2 & C2. The formula shall be in D1 showing the extract data which is B2 from Sheet2.
Can anyone help
The sample data as follow:

Sheet1
A B C D
1 Postal Code Fee Mobile Cross-Chk
2 123456 50 11112222 50
3 222333 30 10102020 20
4 444555 10 13131414 10


Sheet2
A B C D
1Postal Code Fee Mobile
2 123456 50 11112222
3 222333 20 10102020
4 444555 10 13131414

From Sheet1 D2 to D4, I can know that B3 is not the same as D3 which I need to check out the postal code 222333 with my supplier.

Cheers

 

[Aladin Akyurek]

D2, Sheet1, control+shift+enter, not just enter, and copy down:

=INDEX(Sheet2!$C$2:$C$4,MATCH(1,IF(Sheet2!$A$2:$A$4=$A2,
   IF(Sheet2!$B$2:$B$4=$C2,1)),0))

You can have almost anything, like "#", instead of 1 to the same effect.

 

[jamshoot]

D2, Sheet1, control+shift+enter, not just enter, and copy down:

=INDEX(Sheet2!$C$2:$C$4,MATCH(1,IF(Sheet2!$A$2:$A$4=$A2,
   IF(Sheet2!$B$2:$B$4=$C2,1)),0))

You can have almost anything, like "#", instead of 1 to the same effect.

Hi

Copy your code to my worksheet but it didn't work.
I noticed the formula are all referencing sheet2 ... but I am using my supplier info in sheet1 to cross-check.
Sheet2 is my own database used for checking Sheet1 which comes from supplier.
Just wondering why there is no reference to Sheet1.
Cheers

 

[Aladin Akyurek]

Hi

Copy your code to my worksheet but it didn't work.
I noticed the formula are all referencing sheet2 ... but I am using my supplier info in sheet1 to cross-check.
Sheet2 is my own database used for checking Sheet1 which comes from supplier.
Just wondering why there is no reference to Sheet1.
Cheers

If you have a closer look at the post, you'll that you are advised to enter in D2 on Sheet1 the formula

=INDEX(Sheet2!$C$2:$C$4,MATCH(1,IF(Sheet2!$A$2:$A$4=$A2,
   IF(Sheet2!$B$2:$B$4=$C2,1)),0))

which you need to confirm with control+shift+enter, not just enter, and copy down.

 

[jamshoot]

If you have a closer look at the post, you'll that you are advised to enter in D2 on Sheet1 the formula

=INDEX(Sheet2!$C$2:$C$4,MATCH(1,IF(Sheet2!$A$2:$A$4=$A2,
   IF(Sheet2!$B$2:$B$4=$C2,1)),0))

which you need to confirm with control+shift+enter, not just enter, and copy down.

Hi Aladin

Thanks for the reply but I am still getting an error #N/A.
Pardon me that my excel knowledge is limited. I am trying to post here the example using EJHEN add-in so that I can illustrate clearly. But unfortunately, after installing the add-in, it give me crash report about my Mozilla browser.

Cheers

 

[Aladin Akyurek]

Hi Aladin

Thanks for the reply but I am still getting an error #N/A.
Pardon me that my excel knowledge is limited. I am trying to post here the example using EJHEN add-in so that I can illustrate clearly. But unfortunately, after installing the add-in, it give me crash report about my Mozilla browser.

Cheers

My bad. Range references are switched...

Sheet2

Postal Code	Fee	Mobile
123456	50	11112222
222333	20	10102020
444555	10	13131414

<tbody>
</tbody>

Sheet1

Postal Code	Fee	Mobile	Cross-Chk
123456	50	11112222	50
222333	30	10102020	20
444555	10	13131414	10

<tbody>
</tbody>

D2, control+shift+enter, not just enter:

=INDEX(Sheet2!$B$2:$B$4,MATCH(1,IF(Sheet2!$A$2:$A$4=$A2,
   IF(Sheet2!$C$2:$C$4=$C2,1)),0))