Does Excel have any map or select functions?

Question

I have a row of string values, which I'd like to do a vlookup on each and then compute the average of the results. If this were C#, I'd do a Select( str => VLookup(str,dict)).Average(), is there a way to do this in a single excel function?

I'm using version 2010

Answer 1

In general, no. For your specific case, kind of.

Say you have a table like this

a 42
b 2
c 3

in E8:F10. If you call VLOOKUP with an array like this:

=VLOOKUP({"a","c"},E8:F10,2,FALSE)

you'll get back an array of values: {42,3}. (Enter the formula as an array function...Ctrl+Shift+Enter.) So the map part you can do in this case.

Unfortunately, AVERAGE doesn't seem to work with the array that VLOOKUP returns if you put it all in one formula and put the result in one cell. Worse, it appears to work, but just uses the first element, even if you enter the whole thing as an array formula. That is, if you put:

=AVERAGE(VLOOKUP({"a","c"},E8:F10,2,FALSE))

in cell H12, even if you enter it as an array formula, you'll get back 42 and not 22.5.

Oddly, putting the same formula in two cells, say H16:I16, and entering it as an array formula gives you back an array with two elements: {22.5, 22.5}. (It's probbaly just a one-element array getting expanded into two cells.) So you can get what you need without having to have a whole large intermediate array of results. (Using arrays in place of non-array arguments to worksheet functions can be wierd. See: Is there any documentation of the behavior of built-in Excel functions called with array arguments? )

Of course, the more Excel-like way to do this is to use an intermediate array and not try to compress it into a fancy array-formula. You'd have a list of the strings you want to look up, then drag a plain VLOOKUP (with an absolute reference to your lookup table) down/across a parallel row/column, and then average the results.