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.

  • 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 Jun 13 '18 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 Jun 13 '18 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 Jun 13 '18 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 Jun 13 '18 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 Jun 13 '18 at 20:37
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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