Merging tables together

Occasional Contributor

Hi everyone, 

I am wanting one table to read from another. 

Table 1


J4 which is highlighted I would like to have a "1" in it if they have a 1 in any of the columns in j55-n55 ( the top column) of table 2, even if they have multiple 1's on that row still only show a "1" in Table 1. However if they have 0 then it show a "0" in table 1. If row has both, the 1 overrides.
Table 2. 


Thank you in advance. 


4 Replies


=IF(SUM(N(ISNUMBER(SEARCH(1,'table 2'!J55:N55)))),1,IF(SUM(N(ISNUMBER(SEARCH(0,'table 2'!J55:N55)))),0,""))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

The tables are on the same sheet, would that effect the formula?
best response confirmed by Hans Vogelaar (MVP)



You can try this formula. This works in my sample file if both tables are on the same sheet.


Thank you