Excel- How do I find common text values from multiple (>2) columns?

I've got a solution that would work but it's kind of ugly. I'm going to assume you are looking at a cell A1 and seeing if it shows up in all 3 columns (D,E,F).

=MIN(MAX(($D$1:$D$3=$A1)*1),MAX(($E$1:$E$3=$A1)*1),MAX(($F$1:$F$3=$A1)*1))

This is an array formula so it needs to be entered with ctrl+shift+enter

Now how it works starting from the inside out

($D$1:$D$3=$A1)*1 Compare D1 through D3 to A1 which would return true or false the *1 converts this to 1s and 0s

Max If the first formula finds a match in column D it returns a 1 which the Max will return. It no match is found the max (only) value is zero.

Min If all the columns contain a match all the max formulas will contain a 1 so the minimum will be 1. If there isn't a match in all the columns the minimum value will be zero.

If you want to count the number of columns that match use sum instead of min and rememeber ctrl+shift+enter.

note:It will be one max formula for each column you want to search.

This set-up will work for any number of columns.

First go to Name Manager (Formulas tab) and define the following:

Name: Range1
Refers to: =$A$1:$I$8

(Or whatever happens to be the range in question.)

Name: Arry1
Refers to: =COLUMN(Range1)-MIN(COLUMN(Range1))

Name: Arry2
Refers to: =ROW(INDEX(Range1,,1))-MIN(ROW(INDEX(Range1,,1)))+1

Name: Arry3
Refers to: =MMULT(0+(COUNTIF(OFFSET(INDEX(Range1,,1),,Arry1,,),INDEX(Range1,,1))>0),ROW(INDIRECT("1:"&COLUMNS(Range1)))^0)

Exit Name Manager.

The required array formula is then:

=IFERROR(INDEX(INDEX(Range1,,1),SMALL(IF(FREQUENCY(IF(INDEX(Range1,,1)<>"",IF(Arry3=COLUMNS(Range1),MATCH(INDEX(Range1,,1),INDEX(Range1,,1),0))),Arry2),Arry2),ROWS($1:1))),"")

Copy down until you start to get blanks for the results.

Regards

Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just Enter, you first hold down Ctrl and Shift, and only then press Enter. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).