Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 17 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. – codingbadger 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 :) – codingbadger 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 '14 at 10:47

You could use:

index(reference, [row], [column])

where:

reference = Sheet2!$A$1:$A$2

row = match(B1,Sheet2!$D$1:$D$2,0)

column = 1

Cell D1 gets the following value:

= index(Sheet2!$A$1:$A$2,match(B1,Sheet2!$D$1:$D$2,0),1)

Drag these values to cells D2 and D3.

This assumes that your second sheet is called Sheet2, of course.


match(B1,Sheet2!$D$1:$D$2,0)

... will return the row number in array D1:D2 of Sheet2 in which the user name in cell B1 is matched.

share|improve this answer

You can do you your match with the simple VLOOKUP formula if data is within different sheets on the same spreadsheet, but the example from codingbadger is missing an argument (has to have "FALSE" for the formula to only deliver exact matches, it won't work if different or missing).

Sample Usage: VLOOKUP(10003, A2:B26, 2, FALSE)

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

search_key - The value to search for. For example, 42, "Cats", or I24.

range - The range to consider for the search. The first column in the range is searched for the key specified in search_key.

index - The column index of the value to be returned, where the first column in range is numbered 1.

If index is not between 1 and the number of columns in range, #VALUE! is returned. is_sorted - [OPTIONAL - TRUE by default] - Indicates whether the column to be searched (the first column of the specified range) is sorted.

If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.

If is_sorted is set to TRUE or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned.

If is_sorted is FALSE, only an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.

A live example here: https://www.youtube.com/watch?v=WbWoJgdeR-s

Refer to complete guide at: https://support.google.com/docs/answer/3093318?hl=en

share|improve this answer

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.