Populate spreadaheet cell based on value from another cell and another sheet

1
votes

I have a spreadsheet #1 which has rows of data and values in column A such as Product X, Product Y.

I have another spreadsheet #2 with two columns and many rows. Column A is Product X or Product Y and column B is a number value such as 3.50 or 4.50.

Basically I want to automatically populate a cell in Column B in spreadsheet #1 with the values from spreadsheet #2 for each corresponding product.

So somehow the formula will look at column A in sheet #1 and then search for that value in sheet #2 and when it finds that value it will copy the corresponding data from the same row in sheet #2 from Column B.

I have searched and I know this is possible but I cant figure it out myself. I am using Google Sheets btw.

1
google-sheets

1 Answers

0
votes

First, use IMPORTRANGE(spreadsheet_key, range_string) to get a range from one spreadsheet to the other then use a lookup function or similar function to do the lookup.

Remarks:

While it's possible to nest IMPORTRANGE() inside other functions, as it requires the user to authorize the access to another spreadsheet before it access the external spreadsheet for first time, it could be convenient to add a sheet to retrieve the data then use references to that sheet instead of nesting IMPORTRANGE at least until you fully understand how it works.

Frequently used lookup functions

  • VLOOKUP
  • INDEX / MATCH

Other functions

  • LOOKUP
  • HLOOKUP
  • FILTER
  • QUERY

References