Does Excel have any maps or selection features?

Question

I have a string of string values ​​that I would like to do vlookup for each, and then calculate the average of the results. If it were C #, I would do Select( str => VLookup(str,dict)).Average() , is there any way to do this in one excel function?

I am using version 2010

Answer 1

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

Say you have a table like this

 a 42 b 2 c 3 

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

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

You will get an array of values: {42.3}. (Enter the formula as a function of the array ... Ctrl + Shift + Enter.) Thus, you can make part of the map in this case.

Unfortunately, AVERAGE doesn't seem to work with an array that returns VLOOKUP if you put it in one formula and put the result in one cell. Worse, it works, but just uses the first element, even if you enter it all 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 will go back, not 22.5.

Oddly enough, placing the same formula in two cells, say, H16: I16 and entering it as an array formula, you get an array with two elements: {22.5, 22.5}. (Probably only a singleton array expands into two cells.) That way, you can get what you need without having a large intermediate array of results. (Using arrays instead of non-array arguments for worksheet functions can be simpler. See Is there any documentation on the behavior of Excel built-in functions called by array arguments? )

Of course, a more Excel-like way to do this is to use an intermediate array and not try to compress it into a fancy matrix formula. You will have a list of the rows you want to find, then drag a simple VLOOKUP (with an absolute reference to the lookup table) down / through a parallel row / column, and then compare the results.