# Adding in an OR statement to a sum product function

#### courtney27

##### New Member
Hello,

I am trying to write a sum product formula that also includes an OR statement. The value I would like to return is a MatchID based on a Season, Round and Team, however the Team could be one of 2 teams - hence the need for an OR statement. Please see below an example of my data for both the reference table and the sum product table with column references above. Both tables have the same number of rows (1048576).

A C D G
Season Round MatchID Team
2003 16 ? EFC
2004 17 ? BFC
2005 3 ? CFC

Season Round Team1 Team2 MatchID
2003 16 COFC EFC 5
2004 17 BFC PAFC 10
2005 3 FRFC CFC 9

Here is what I have tried so far (which returns a "0"). I read on a forum that the "+" could be used to simulate the "OR".

Please let me know where I am going wrong and any suggestions for getting the right answer. Thank you for your help.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try to avoid references to entire columns, or almost, like V2:V1048576. This type of reference to huge ranges seriously impair the spreadsheet performance. Try to limit the ranges to the actual or the maximum expected number of rows.

Maybe this...

Formula in D2 copied down

Hope this helps

M.

Last edited:
Thanks Marcelo, this worked well. Unfortunately, my data set is huge and the actual number of rows is 1048576.
Thanks again!

You are welcome.

Good luck!

M.

Thanks Marcelo, this worked well. Unfortunately, my data set is huge and the actual number of rows is 1048576.
Thanks again!

In AE2 (or in a cell more convenient) enter and copy down:

=V2&"|"&W2&"|"&Y2&"|"&Z2

When done, try to run:

This set up trades off space (memory) against time (speed).

1,214,657
Messages
6,120,769
Members
448,991
Latest member
Hanakoro

### 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.

### Which adblocker are you using?

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