Vlookup using ArrayFormula and IMPORTRANGE

[dcs]

hey, I am new and existed to get some automated flows. 

I started using vlookup to refer details from another Google sheet.  I am using from another Gsheet. I am using ArrayFormula and vlookup where I am using IMPORTRANGE to refer details from another google sheet.  It started working well.  

When my rows increased, the output started jumbling the rows from original sheet.

For eg: in Target Sheet for Unique ID : Bank20212, vlookup is fetched details from unique id of Bank2021199. Similarly for Unique id Bank202120 values of unique id Bank2021199 are getting fetched.

Formula that I have:  replaced xxxxx to mask actual values. 

=ArrayFormula(if($B3:B="","",vlookup($B3:B,IMPORTRANGE("://docs.google.com/spreadsheets/d/1bxxxxxxxxxxxxxxxxxxxx/edit#gid=0 ","FullTxns!C:U"),E$1)))

screen showing the same values against Unique ID (SrcRef) 

Please help. 

[WillowMobileSys]

This is an AppSheet forum.  Is your issue a problem with AppSheet or within the Google sheet only? 

[dcs]

I am using google sheets for AppSheet form.  I will request support on Google sheets. Tks. 

[scott192 · Accepted solution]

Dont put your IMPORTRANGE function inside an ArrayFormula!  Your sheet will be importing and searching the same range for every row that is computed.  Add a blank sheet and import your range into that sheet once.  Then just use the same lookup formula, but refer to your new sheet instead of the importrange one.

[dcs]

Thanks Scott192.  Will try that option. 

[dcs]

It worked. I have created multiple sheets and then applied vlookup. 

[stachu541]

Also, you may want to consider INDEX-MATCH for speed. Vlookup is heavy.