0

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
      (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.

G-Man Says 'Reinstate Monica'
  • 7,678
  • 22
  • 41
  • 87
decibel
  • 23
  • 2
  • 1
    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
  • Why will someone give my question a minus 1? – decibel Feb 25 '17 at 16:31
  • @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
  • 1
    @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](//meta.unix.stackexchange.com/q/4086/80216) (see [here](http://superuser.com/q/889201/354511) and [here](http://superuser.com/q/892744/354511) 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](http://superuser.com/questions/785128/ms-excel-assigning-categories-based-on-keywords) – G-Man Says 'Reinstate Monica' Feb 25 '17 at 16:41

1 Answers1

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.

G-Man Says 'Reinstate Monica'
  • 7,678
  • 22
  • 41
  • 87
  • 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