Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a page which looks like this:

╔═══╦════════╦══════╦═══╗
║ A ║ B      ║ C    ║ D ║
╠═══╬════════╬══════╬═══╣
║ 1 ║ User A ║ 144  ║   ║
║ 2 ║ User B ║ 5478 ║   ║
║ 3 ║ User A ║ 2156 ║   ║
╚═══╩════════╩══════╩═══╝

I'd like to populate column D with data from another page:

╔═════╦══════╦═══╦════════╗
║ A   ║ B    ║ C ║ D      ║
╠═════╬══════╬═══╬════════╣
║ ABC ║ User ║ B ║ User B ║
║ DEF ║ User ║ A ║ User A ║
╚═════╩══════╩═══╩════════╝

Note: column D is a concatenation of column B and C.

In this case, it should populate column D on page 1 with the data from column A on page 2 matching Page1!B to Page2!D, which should produce the following combined table:

╔═══╦════════╦══════╦═════╗
║ A ║ B      ║ C    ║ D   ║
╠═══╬════════╬══════╬═════╣
║ 1 ║ User A ║ 144  ║ DEF ║
║ 2 ║ User B ║ 5478 ║ ABC ║
║ 3 ║ User A ║ 2156 ║ DEF ║
╚═══╩════════╩══════╩═════╝

How can I do this in Google Spreadsheets?

share|improve this question
    
1  
I'm not sure. The other question is about linking to another document and the solution is different. –  ChrisF Dec 16 '10 at 21:16

1 Answer 1

up vote 12 down vote accepted

You need to use the VLOOKUP function

I have the following in Sheet1 alt text

And this in Sheet 2

alt text

I have assigned a range to the values in Sheet2 and called the range LookupValues.

Then in my formula for column D in sheet 1 I have: alt text

A breakdown of the formula is:

  • A3 is the value to find in the lookup range
  • LookupValues a range to search through
  • 3 the index of the range to return if the value (A3) is found in the range (LookupValues)

UPDATE

To create a range you right click the sqaure in the top left of the spreadsheet and select Define named range

alt text

You then enter the nickname or alias you want the range to be known/referenced as and the range of cells you want to be available in the range.

alt text

You can then access the range of cells by the name rather than the traditional Sheet2!A1:T100 method.

EDIT 2

In response to your updated question you will need to change the formula to this:

=VLOOKUP(B1, LookupValues, 1)

This will search through your range for the value in B1 from Sheet1. e.g "User A" and then return whatever value is in column 1 e.g "ABC"

share|improve this answer
    
Could you please explain which range you are using for LookupValues? I also modified the example a bit to reflect my actual problem better. –  Senseful Dec 16 '10 at 10:26
    
@Senseful - I have updated my answer. If you have any further problems let me know. –  Barry Dec 16 '10 at 10:42
    
Thanks, that works. The confusing part was that you don't specify which column to use to look up the information on the second sheet. Instead, it uses the first column in the range specified as the second parameter. I ended up duplicating the columns at the end of my spreadsheet so that they can be used for lookup. –  Senseful Dec 16 '10 at 11:29
    
Yes its the same in MS Excel. I always used to try and say "lookup from this column, look in the column and give me this column" rather than provide the range. It's a strange one :) –  Barry Dec 16 '10 at 11:33
    
In Google Docs (spreadsheet) this is now called "Named and protected ranges" in the right click menu. –  ing0 Feb 3 at 10:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.