-1

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

https://meta.stackexchange.com/questions/22922/which-site-do-excel-or-other-spreadsheet-formulas-belong-on?lq=1

  • 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
0

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.

  • 1
    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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.