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

jamshoot

Board Regular
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

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

MrExcel MVP
D2, Sheet1, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=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

Board Regular
D2, Sheet1, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=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

MrExcel MVP
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
Rich (BB code):
``````=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

Board Regular
If you have a closer look at the post, you'll that you are advised to enter in D2 on Sheet1 the formula
Rich (BB code):
``````=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.

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

MrExcel MVP

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:
Rich (BB code):
``````=INDEX(Sheet2!\$B\$2:\$B\$4,MATCH(1,IF(Sheet2!\$A\$2:\$A\$4=\$A2,
IF(Sheet2!\$C\$2:\$C\$4=\$C2,1)),0))``````

Replies
20
Views
447
Replies
10
Views
639
Replies
1
Views
260
Replies
8
Views
196
Replies
10
Views
260

1,195,597
Messages
6,010,641
Members
441,558
Latest member
lambierules

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back