Finding values in multiple columns

[SBuckley]

So I am trying to find numbers that appear in more than two columns, I have a formula that works for two columns and I need to expand on that so that it looks into three columns.
The formula I have gives me either the number if it is found in the other column or a blank if it only appears in the one.

Here is the formula:
=IF(ISERROR(MATCH('[Excess Water Usage Spreadsheet.xls]Excessive_Users_2014'!G3,'[Excess Water Usage Spreadsheet.xls]Excessive_Users_2014'!$H$3:$H$4000,0)),"",'[Excess Water Usage Spreadsheet.xls]Excessive_Users_2014'!G3)

Any help is greatly appreciated.

Thanks,

Scott

 

[venkat1926]

I am not sure I have understood your problem. see data here and formula in E1 (which is copied down). Is this what you want. This is only if a value in columns A is available in column B OR column C

Sheet1

*	A	B	C	D	E
1	6	5	7	*	*
2	10	9	1	*	10
3	5	8	5	*	5
4	4	4	2	*	4
5	3	1	10	*	*
6	2	10	1	*	2
7	1	10	2	*	1
8	6	2	9	*	*
9	5	10	8	*	5
10	7	4	4	*	7

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 =IF(IF(ISERROR(MATCH(A1,$B$1:$B$10,0)),0,MATCH(A1,$B$1:$B$10,0))+IF(ISERROR(MATCH(A1,$C$1:$C$10,0)),0,MATCH(A1,$C$1:$C$10,0))=0,"",A1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[SBuckley]

I will try to play around with that formula to get what I want, I just want to get the numbers that appear all three times and not the ones that appear twice. This is because I will need to do one thing with the numbers I get from the equation I posted but then I have to do something else with the second set. Below is the table with what I'm looking to get.

A	B	C	D
55	100	100	100
66	55	89
77	44	66	66
100	66	33
89	29	55	55

<tbody>
</tbody>

Thanks for your help!

 

[SBuckley]

And column D is where the formula would be applied to look through column C.

 

[SBuckley]

Ok just for a head's up and for anyone else who may ever have the same question. I was able to fix the code to search all three columns and find the matching values in all three.

=IF(AND(MATCH(C1,$B$1:$B$5,0),MATCH(C1,$A$1:$A$5,0)),C1,"")