1

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.

Improve this question

1 Answer 1

Reset to default
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:

VLOOKUP formula example 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.

Improve this answer
6
  • I forgot to set FALSE for exact match on lookup in the formula. Amended.
    – kopischke
    Oct 25, 2011 at 16:32
  • This is what I ended up with for my code. Thanks so much: =(VLOOKUP(A1,'A-D/A'!A:K,4)-VLOOKUP(A1,'A-D/A'!A:K,5))/VLOOKUP(A1,'A-D/A'!A:K,4)
    – Dorje
    Oct 25, 2011 at 16:53
  • Actually, this is the code: =(VLOOKUP(A1,'A-D/A'!A:K,4,FALSE)-VLOOKUP(A1,'A-D/A'!A:K,5,FALSE))/VLOOKUP(A1,'A-D/A'!A:K,4,FALSE)
    – Dorje
    Oct 25, 2011 at 17:14
  • Looks like you got this figured out. Glad if my answer helped.
    – kopischke
    Oct 25, 2011 at 17:16
  • This will help a County department have a dashboard to evaluate their quality improvement progress; so, this will help many people. Folks in my office are stunned about how quickly we got an answer. I think I've made some converts to stackexchange. Thanks a ton.
    – Dorje
    Oct 25, 2011 at 17:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .