An update to vlookup and arrayformula

For those trying to use arrayformula in Google Spreadsheets, I thought I would give a few examples that might help when designing your function.  In addition, I've provided a general example where you can fill in your corresponding ranges to build the function.  Let me know if you have any problems!

To customize use this key.

=arrayformula(if(row([range1])=1,"Impossible",vlookup([range1],[range2],{N}*sign(row([range1])),FALSE)))

where

[range1]: range of values to be looked up

[range2]: table of data where look up values are in column 2

N: number of column that has the data you want to return


I've also embedded a spreadsheet here that shows a few examples of how to use this:


Also, if you want to play with this spreadsheet feel free to make your own copy here:
Using arrayformula with vlookup template

Cheers!

Counting Unique Values of a Data Set

Here is a quick How-To from the Spreadsheets forum.  There are a few ways to do this in Google Docs but I wanted to highlight the use of the =query() function which uses GViz.  See these quick links for Google's Documentation:
Using =query()
Google Visualization API Query Language

I know "API" and "Visualization" and "Query Language" sound scary but they really aren't!  In this quick example I'll show you how to sum up data for unique values!

Consider the following data: Ted 5, Yogi 6, Adam 9, Ted 7, Adam 11, Yogi 13, Ted 6.
This example is meant for much larger data sets where there could be many different names of people with many different data points. Basically, the query() function will help us sum up the numbers next to each person's name by person and sort by the highest sum.


Breakdown of function from the center of the nest:
=query(A2:B8,"Select sum(B) pivot (A)") : This is the query used to collect the data that I would like. A2:B8 indicate the range of the data I'm going to query over. "Select sum(B) pivot(A)" says that I want to select the sum of column B by the values in column A. In spreadsheets world, this is basically a pivot table where you pivot on on the unique values from column A. This function alone puts the unique values (names) as their own column and puts the sum under it. However, most of the time it is easier to view this as the user in the post requested. See the Just query() tab above.


=transpose(query(A2:B8,"Select sum(B) pivot (A)")): This transposes the table. Basically it turns columns into rows and rows into column. See the transpose(query()) tab.


=sort(transpose(query(A2:B8,"Select sum(B) pivot (A)")),2,FALSE): The final step sorts the table by the summed column. The 2 indicates the second column in the table is the column to sort by and FALSE indicates to sort in descending order.

Happy Holidays and the coming weeks

Wow. I realized I posted ONCE in November. Not good.  Anyways, a lot has been going on with Docs and I hope to post more soon!  The past couple of weeks have been pretty crazy but I plan on doing a few more analysis posts about the forum itself, the future of Google Docs and try to apply some of what has been going on to a few models about change.  It would be really great to help users learn how to be the most resilient Google product users!  I do think some of this learning is going on by newer users but I hope a few of my insights will help others see their use of Google Docs from a new light.

However, I probably won't publish these posts for a few weeks (and hopefully from my new Chromebook!).  For now, enjoy this image I made using G+. Happy Holidays!!