Microsoft Excel and Google Sheets

Question

The formula in Microsoft Excel is not any different than in the Google Sheets. But, I get different results

(left) Microsoft Excel, (right) Google Sheets

The Google Excel formula works; the same Google Sheets formula does not work in the Microsoft Excel one.

How can I fix this? Ignore the Dutch separator ; – in the English version with the separator , I have the same problem.

Is your question ultimately how do I get this formula to work in Microsoft Excel? If so, please edit your question to clarify that. In particular asking Is it Google Excel? makes it unclear which question you're asking. — I say Reinstate Monica, Feb 25 '17 at 15:46
Good edit. This is a lot more focused. However, in which is the formula correct (Google Sheets or MS Excel?) — I say Reinstate Monica, Feb 25 '17 at 15:50
@Twisty: The question says, “left Microsoft Excel, right Google Sheets” / “The Google Excel formula works, the same … formula does not work in the Microsoft Excel one.” Also, the Google Sheet (the one on the right) is showing intelligible results in Column D, where the Excel worksheet (the one on the left) is showing “other” in every row. — G-Man Says 'Reinstate Monica', Feb 25 '17 at 16:33
@G-Man I asked my clarifying questions before the OP edited his answer in response to them. — I say Reinstate Monica, Feb 25 '17 at 16:34
@decibel :I didn’t down vote you, but I am unhappy that (1) you posted an image of data without posting it in textual form (see here and here for examples of how to do it), and (2) you’re asking for help on a formula with five function calls without explaining what it’s supposed to do. Other people probably feel similarly. — G-Man Says 'Reinstate Monica', Feb 25 '17 at 16:40
If the objective is to get the desired functionality working in Excel, this is a possible duplicate of MS Excel – assigning “categories” based on keywords — G-Man Says 'Reinstate Monica', Feb 25 '17 at 16:41

score 4 · Accepted Answer · answered Feb 25 '17 at 16:51

4

The problem on the Excel side is that you seem to be expecting SEARCH(range, value) to return an array, but, as far as I can tell from the question, you have not entered it as an array formula. If you click in cell D2 and press Ctrl+Shift+Enter, your formula will work.

answered Feb 25 '17 at 16:51

G-Man Says 'Reinstate Monica'

7,827

I think that to perform an array formula in Google Sheets, you need to preface the formula with =(ArrayFormula( instead of the button combo above. – O.Allen Apr 17 '18 at 08:58
@O.Allen: Well, I don’t know much about Google Sheets, so I won’t dispute what you’re saying — except to point out that, in the image in the question, it appears that SEARCH(F$2:F$5, A2) is returning an array (like {10, #VALUE, #VALUE, #VALUE}) in Google Sheets without using ArrayFormula. – G-Man Says 'Reinstate Monica' Apr 17 '18 at 17:55

Microsoft Excel and Google Sheets

1 Answers1