1

Google Sheets: I have two google files. Lets call them "Data" and "Sales"

I need a formula in "Sales" cell that will search a column in "Data" by matching a cell value in "Sales" to a cell value in "Data".

I am familiar with importRange, index and vlookup formulas but am stuck.

Improve this question
5
  • 2
    The title of the question is very vague, please improve it. What formula you tried? If you got an error message add it textually. Also add a brief description of your search/research efforts. Ref How to Ask.
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 13, 2018 at 1:56
  • I used importRange but don't know how to go further. I need the formula to index and find the information on "Data" before it imports it into "Sales"
    – Anwaar Hassan
    Commented Jun 13, 2018 at 13:34
  • By using built-in formulas first we have to use IMPORTRANGE to import the data into the destination sheet, then we could use functions like VLOOKUP, QUERY, INDEX and MATCH among others to return a value or array of values that meet certain conditions. There are a lot of questions on this site about using this functions. If you need further help add more details in order to understand where are you stuck, otherwise the question is too broad for this site.
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 13, 2018 at 13:45
  • Thanks for responding. Understood the process and am familiar with the functions you mentioned. Is it possible to combine all functions into one formula. Otherwise it appears that I would have to import the data to the destination file on a blank sheet and then index/match the info. Is there anyway to avoid creating an additional sheet to import the data?
    – Anwaar Hassan
    Commented Jun 13, 2018 at 20:33
  • Yes it's possible. For further help, please edit your question and add a brief description of your search/research efforts.
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 13, 2018 at 20:37

1 Answer 1

Reset to default
-1

example of such combination would be:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(LEN(IMPORTRANGE("ID"; "Sales!A2:A")); 
 VLOOKUP(IMPORTRANGE("ID"; "Sales!A2:A"); IMPORTRANGE("ID"; "Data!A2:B"); 2; 0); )); 1; 1)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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