<p>Effectively I want to give numeric scores to alphabetic grades and sum them. In Excel, putting the <code>LOOKUP</code> function into an array formula works:</p>

<pre><code>{=SUM(LOOKUP(grades, scoringarray))}
</code></pre>

<p>With the <code>VLOOKUP</code> function this does not work (only gets the score for the first grade). Google Spreadsheets does not appear to have the <code>LOOKUP</code> function and <code>VLOOKUP</code> fails in the same way using:</p>

<pre><code>=SUM(ARRAYFORMULA(VLOOKUP(grades, scoresarray, 2, 0)))
</code></pre>

<p>or</p>

<pre><code>=ARRAYFORMULA(SUM(VLOOKUP(grades, scoresarray, 2, 0)))
</code></pre>

<p>Is it possible to do this (but I have the syntax wrong)? Can you suggest a method that allows having the calculation in one simple cell like this rather than hiding the lookups somewhere else and summing them afterwards?</p>

[{'answer_id': 28132, 'author': 'paulmorriss', 'author_id': 2983, 'author_profile': 'https://Stackoverflow.com/users/2983', 'pm_score': 2, 'selected': False, 'text': '<p>I\'m afraid I think the answer is no. From the help text on\n<a href="http://docs.google.com/support/spreadsheets/bin/answer.py?answer=71291&amp;query=arrayformula&amp;topic=&amp;type=" rel="nofollow noreferrer">http://docs.google.com/support/spreadsheets/bin/answer.py?answer=71291&amp;query=arrayformula&amp;topic=&amp;type=</a></p>\n\n<blockquote>\n  <p>The real power of ARRAYFORMULA comes when you take the result from one of those computations and wrap it inside a formula that does take array or range arguments: SUM, MAX, MIN, CONCATENATE,</p>\n</blockquote>\n\n<p>As vlookup takes a single cell to lookup (in the first argument) I don\'t think you can get it to work, without using a separate range of lookups.</p>\n'}, {'answer_id': 29690, 'author': 'Sam Brightman', 'author_id': 2492, 'author_profile': 'https://Stackoverflow.com/users/2492', 'pm_score': 1, 'selected': True, 'text': '<p>I still can\'t see the formulae in your example (just values), but that is exactly what I\'m trying to do in terms of the result; obviously I can already do it "by the side" and sum separately - the key for me is doing it in one cell.</p>\n\n<p>I have looked at it again this morning - using the <code>MATCH</code> function for the lookup works in an array formula. But then the <code>INDEX</code> function does not. I have also tried using it with <code>OFFSET</code> and <code>INDIRECT</code> without success. Finally, the <code>CHOOSE</code> function does not seem to accept a cell range as its list to choose from - the range degrades to a single value (the first cell in the range). It should also be noted that the <code>CHOOSE</code> function only accepts 30 values to choose from (according to the documentation). All very annoying. However, I do now have a working solution in one cell: using the <code>CHOOSE</code> function and explicitly listing the result cells one by one in the arguments like this:</p>\n\n<pre><code>=ARRAYFORMULA(SUM(CHOOSE(MATCH(D1:D8,Lookups!$A$1:$A$3,0),\n                                     Lookups!$B$1,Lookups!$B$2,Lookups!$B$3)))\n</code></pre>\n\n<p>Obviously this doesn\'t extend very well but hopefully the lookup tables are by nature quite fixed. For larger lookup tables it\'s a pain to type all the cells individually and some people may exceed the limit of 30 cells.</p>\n\n<p>I would certainly welcome a more elegant solution!</p>\n'}, {'answer_id': 14738977, 'author': 'HHains', 'author_id': 2048572, 'author_profile': 'https://Stackoverflow.com/users/2048572', 'pm_score': 0, 'selected': False, 'text': "<p>I know this thread is quite old, but I'd been struggling with this same problem for some time.  I finally came across a solution (well, Frankenstiened one together).  It's only slightly more elegant, but should be able to work with large data sets without trouble.</p>\n\n<p>The solution uses the following:</p>\n\n<pre><code>=ARRAYFORMULA(SUM(INDIRECT(ADDRESS(MATCH(), MATCH())))\n</code></pre>\n\n<p>as a surrogate for the vlookup function.</p>\n\n<p>I hope this helps someone!</p>\n"}, {'answer_id': 51566590, 'author': 'pnuts', 'author_id': 1505120, 'author_profile': 'https://Stackoverflow.com/users/1505120', 'pm_score': 1, 'selected': False, 'text': '<blockquote>\n  <p>Google Spreadsheets does not appear to have the LOOKUP function</p>\n</blockquote>\n\n<p>Presumably not then but it does have now:</p>\n\n<p><a href="https://i.stack.imgur.com/l4Qo6.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/l4Qo6.gif" alt="SO27774 example"></a></p>\n\n<p><code>grades</code> Sheet1!A2:A4<br>\n<code>scoringarray</code> Sheet1!A2:B4</p>\n'}, {'answer_id': 55196805, 'author': 'player0', 'author_id': 5632629, 'author_profile': 'https://Stackoverflow.com/users/5632629', 'pm_score': 0, 'selected': False, 'text': '<p>you can do so easily like this by hardcoding it in VR table:</p>\n\n<pre><code>=SUM(IFERROR(ARRAYFORMULA(VLOOKUP(A2:A, {{"A", 6};\n                                         {"B", 5};\n                                         {"C", 4};\n                                         {"D", 3};\n                                         {"E", 2};\n                                         {"F", 1}}, 2, 0)), ))\n</code></pre>\n\n<p><a href="https://i.stack.imgur.com/vVS8B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vVS8B.png" alt="0"></a></p>\n\n<p>or you can use some side cells with rules:</p>\n\n<pre><code>=SUM(IFERROR(ARRAYFORMULA(VLOOKUP(A2:A, E2:F, 2, 0)), ))\n</code></pre>\n\n<p><a href="https://i.stack.imgur.com/hd1GN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hd1GN.png" alt="6"></a></p>\n\n<hr>\n\n<p><strong><sub>alternatives: <a href="https://webapps.stackexchange.com/a/123741/186471">https://webapps.stackexchange.com/a/123741/186471</a></sub></strong></p>\n'}]
['https://Stackoverflow.com/questions/27774', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2492/']