How do I lookup a value from another sheet in Google Docs?

Question

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?

Answer 1

You need to use the VLOOKUP function

I have the following in Sheet1

And this in Sheet 2

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:

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

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.

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"

Answer 2

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.

Answer 3

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