I have three columns of data in columns K, M and O in a Google Spreadsheet and want to find out (with results printed in column H) which values are common to each of K,M and O.

To make it simpler, I tried to compare two columns K and M with this formula

``````=ARRAYFORMULA((IFERROR(MATCH(K1:K,M1:M,0),0)))
``````

The problem with the results was that it printed (in column H) the number of the row in column M where the value in column K is located. I'd just rather have the value printed in column H rather than a number telling me where to find the value in column M, so there's that problem, and then that only works for two columns. Also, for a comparison of three columns to work, it can't print a number telling me where to find a value because it'd have to print two numbers (one for each of the other two columns), so I need the value printed. How can I add a third column into here?

Note, I wasn't sure about where to ask this type of question, however, I found this answer below saying that Excel "formula" questions can be asked here, so I assumed the same for Google Spreadsheets. I tagged this question as Excel as well because I think many of the basic formulas are the same

Update

I think this might be complex with three columns, so if you can show me how to do it with two columns P and Q, I can compare the results of that formula with column R and in that way find the common values in all three columns. However, for that to work, the comparison of two columns has to print the matching value in a new column so that new column can be compared with the third column

• I suppose you are able to take the data over to Excel for processing, it might be a lot easier to do in a VBA script. You could have that loop through all three columns and print out the entries it finds in the other two. – TheUser1024 Jul 13 '14 at 13:49
• @TheUser1024 I don't have Excel. I'm wondering is there a way to nest another MATCH call with the results of the first? – Michael Jul 13 '14 at 13:50
• Okay, maybe add that requirement to your question because the task is so asking to be scripted that i am tempted to suggest CSV export+PowerShell or your OS's equivalent. ;-) But you want it in your spreadsheet for future use, correct? This is the right place for such questions btw. and a good one on top. – TheUser1024 Jul 13 '14 at 13:55

If I correctly understand what is returned, then

=ARRAYFORMULA(iferror(offset(M1,IFERROR(MATCH(K1:K,M1:M,0),-1)),0),0)

should work.

Edit: The parenthesis were incorrectly placed.

=ARRAYFORMULA(iferror(offset(M1,IFERROR(MATCH(K1:K,M1:M,0),-1),0),0))

The only way to get another column in the comparison is to have another MATCH statement and to see if the 2 MATCH statements are searching for the same word. It would be easier to program a VBA macro for this task.

• Is there a typo in your answer. Your solution only compares two columns (K and M) and I'm looking for the common values in three columns. Also, I got an error `Error: Wrong number of arguments to ARRAYFORMULA. Expected 1 arguments, but got 2 arguments.` – Michael Jul 13 '14 at 14:07
• @Michael I don't have Excel at home, so I couldn't verify the statement. I believe that I have a closing parenthesis out of place. It should be ...0),-1),0),0)) – LDC3 Jul 13 '14 at 14:55
• don't think this is right. It's only printing one value but there should be many (based on my naked eye comparison of the two columns), so I don't think your formula is going past the first row somehow – Michael Jul 13 '14 at 15:28
• IDK if MATCH return only the first match, or every match; check the help info. Also the ARRAYFORMULA may be just applying itself to the OFFSET statement and not to the MATCH, so it would need to be re-positioned or a second one added. Like I said, a VBA macro would be easier. – LDC3 Jul 13 '14 at 15:52