I know I'm chaining a lot together here, but it's SO close to functional. Here is my formula with the doc address altered for privacy.

=ARRAYFORMULA(IF(ISBLANK($AE$3:$AE),(query(importrange("https://docs.google.com/spreadsheets/d/XXXDkgregmq16N91iBOjKcMH3B74w9PoEExrHRWGdaP4/edit#gid=1834499049","Brite Fills Lot Truncation!A1000:H"),"select Col2, Col6 Where Col1= '"&('14 oz Bottles'!$G3:$G)&"'"))))

In short, I want this formula to auto-fill into a sheet that collects responses for a Google Form. It cross references another sheet and returns data based on the value of the G column in my current sheet. The only issue is what is in bold above. This function ONLY returns the value that matches $G$3 in my current sheet, acting as if it's an absolute cell reference. I want it to be relative so that it fills each line based on whatever value is in the G cell on that row.

I would be thankful for any insight!

  • for that, you will need to add vlookup. share a copy (dummy) of your sheet for further help – user0 May 21 at 8:15
  • docs.google.com/spreadsheets/d/… I hope you were asking for a dummy copy of the form and not calling me a dummy. Though if you were, I respect your intentionally ambiguous word use. Will this work as a dummy copy? ArrayFormula is in AF2. I intentionally changed the target document for ImportRange to respect the confidentiality of my company, but it finds the data in column 2 and 6 of the target sheet where column 1 is the value in column G of this sheet. The array only returns the values corresponding to G3 on this sheet – Riley Clawson May 22 at 5:11
  • I had a look on your sheet, unfortunately, the data you left there are insufficient to understand what exactly are you after. – user0 May 22 at 13:42

Your Answer

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

Browse other questions tagged or ask your own question.