How can I change one cell value if it matches another in my spreadsheet?

Jens Petter Røyseth May 31, 2013


My problem is this:

I have a spreadsheet with values in Column A and Column B, both are related. In A I have names, and in B I have ages. How can I have an input field where if I write a name in cell C1, that matches any name in A (the names), I will get the age automatically in cell D1?

  1. Oron Joffe
    May 31, 2013 at 12:31 pm

    As Rajaa says, the answer is VLOOKUP. Using the example you gave (A=name, B=age, C=input area, D=calculated value, and assuming that your list goes from row 2 to 10, the function would look like this:

    You can read more about it in your spreadsheet's help, but there are two things I can point out to you now, as they can be confusing for those new to lookups: 1. The search range (second argument) has to span BOTH the searchable area (col. B in this case) and the results (col. C). Thus, it has to be at least two columns wide. The second thing is that the index (third argument) starts with 1, so typing 1 would return the first column (the name). In practice, this means you'd always use an index of 2 or more (if you had more than two columns).

    • Bruce Epper
      May 31, 2013 at 4:03 pm

      The searchable area would be column A, not B and the results would be column B, not C.

    • Oron Joffe
      May 31, 2013 at 4:27 pm

      Oops! You're right of course Bruce, A & B, not B & C!

  2. Rajaa Chowdhury
    May 31, 2013 at 2:24 am

    I think you should simply use the VLookup function of excel for it.