How would I create a function in Google Docs (or Excel) to evaluate column a and return column b value.

Question

I'm using google docs speadsheets to create a dashboard. I have two columns of data. I want to have a cell evaluate Column One for a specific name(for example, "Agency A") and return the value in Column Two that is associated with that agency. I've tried a couple of different things, but my thinking is all off I think.

• Column 1 Column 2
• Agency A Value A
• Agency B Value B

In this case, I would want it would return Value A if it finds Agency A in Column 1.

Answer 1

What you are trying to achieve is a lookup by vertical index, where the key value is in column 1 and the value in column 2. Excel supports this via the VLOOKUP function. If column 1 = column A, column 2 = column B and the value to look up = C1:

=VLOOKUP(C1,$A:$B,2,FALSE)

will return the associated value, as in this screenshot:

You can also restrict the range being looked up if the columns contain other values, i.e. use A1:B2 (but then be careful of rows being missed when you add key value pairs).

According to its documentation, Google Spreadsheets supports the VLOOKUP formula with the same syntax as Excel, so you should be fine there too.