0

I need help with the Google Sheet formula. I am trying to combine the array formula with vlookup and import range to get specific values from three columns from two different sheet

For example, if you write c15, it will look for this specific value in the first or the second sheet. When finding it, it will get the value from the chosen columns and write it in the new sheet. The formula works perfectly with one file, but I want the formula to vlookup within the two sheets

This is the formula

=ArrayFormula(VLOOKUP(C4,IMPORT RANGE("URL","Autoparts!A:AC"),{5,29,10},0))

C4 is the cell that has the value I'm looking for. {5,29,10} These are the columns where to look for the needed values.

Any help is appreciated.

Osm
  • 2,699
  • 2
  • 4
  • 26
HY JAPAN
  • 1
  • 1
  • one more thing, please. one of the import range sheets has more than 30k rows with 30 columns, how to split the import range to avoid "results too large" error? – HY JAPAN Oct 23 '22 at 12:42

2 Answers2

1

Use this

It simply an array {} that contain the result of the two vlookup's stacked using ; in US Local.

See more about locals on Set a spreadsheet’s location & calculation settings

=ArrayFormula({
 VLOOKUP(C4,IMPORTRANGE("URL1","Autoparts!A:AC"),{5,29,10},0); 
 VLOOKUP(C4,IMPORTRANGE("URL2","Autoparts!A:AC"),{5,29,10},0)})

This will return

Url1 Column 5 value | Url1 Column 29 value | Url1 Column 10 value   
Url2 Column 5 value | Url2 Column 29 value | Url2 Column 10 value

If you want to use lambda with more vlookup's you only need to change C4 in one place for convenience 

=ArrayFormula(
 LAMBDA (v, {
 VLOOKUP(v, IMPORTRANGE("URL1","Autoparts!A:AC"),{5,29,10},0); 
 VLOOKUP(v, IMPORTRANGE("URL2","Autoparts!A:AC"),{5,29,10},0)})
        (C4))

Demo for more urls

=ArrayFormula(
 LAMBDA (v, {
 VLOOKUP(v, IMPORTRANGE("URL1","Autoparts!A:AC"),{5,29,10},0); 
 VLOOKUP(v, IMPORTRANGE("URL2","Autoparts!A:AC"),{5,29,10},0);
 VLOOKUP(v, IMPORTRANGE("URL3","Autoparts!A:AC"),{5,29,10},0);
 VLOOKUP(v, IMPORTRANGE("URL4","Autoparts!A:AC"),{5,29,10},0); 
 VLOOKUP(v, IMPORTRANGE("URL5","Autoparts!A:AC"),{5,29,10},0);
 VLOOKUP(v, IMPORTRANGE("URL6","Autoparts!A:AC"),{5,29,10},0)})
        (C4))
Osm
  • 2,699
  • 2
  • 4
  • 26
0

to search within multiple importranges you can do:

=ARRAYFORMULA(IFNA(VLOOKUP(C4, 
 {IMPORTRANGE("URL1", "Autoparts!A:AC");
  IMPORTRANGE("URL2", "Autoparts!A:AC");
  IMPORTRANGE("URL3", "Autoparts!A:AC")}, {5, 29, 10}, )))

if for example, 2nd importrange is 30k rows you can split it like this:

=ARRAYFORMULA(IFNA(VLOOKUP(C4, 
 {IMPORTRANGE("URL1", "Autoparts!A:AC");
  IMPORTRANGE("URL2", "Autoparts!A1:AC15000");
  IMPORTRANGE("URL2", "Autoparts!A15000:AC30000");
  IMPORTRANGE("URL3", "Autoparts!A:AC")}, {5, 29, 10}, )))

but since you want only 3 columns it would be better to try:

=LAMBDA(u1, u2, u3, ARRAYFORMULA(IFNA(VLOOKUP(C4,
 {IMPORTRANGE(u1, "Autoparts!A:J"), IMPORTRANGE(u1, "Autoparts!AC:AC");
  IMPORTRANGE(u2, "Autoparts!A:J"), IMPORTRANGE(u2, "Autoparts!AC:AC");
  IMPORTRANGE(u3, "Autoparts!A:J"), IMPORTRANGE(u3, "Autoparts!AC:AC")}, 
 {5, 11, 10}, ))))(
 "https...url1...here",
 "https...url2...here", 
 "https...url3...here")
player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you for your answers, really appreciated, I tried to apply but got an error sorry for being unclear, but I want the vlookup to search for value in the C4 and import only results related to that value in the new three columns. – HY JAPAN Oct 24 '22 at 05:42
  • this is the error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows. – HY JAPAN Oct 24 '22 at 06:06
  • @HYJAPAN [ARRAY_LITERAL](https://stackoverflow.com/a/58042211/5632629) is probably due to one or multiple of your importranges are not working. before using suggested formulae from the answer you need to run each and every importrange formula separately as standalone fx to connect your spreadsheets – player0 Oct 24 '22 at 08:14
  • I checked the importranges formula and working fine, but the problem is when I try to split the 30k importrange. I want the formula to return one value in one row, not two values. Sorry for inconvenience in this formula . =ARRAYFORMULA(IFNA(VLOOKUP(C4, {IMPORTRANGE("URL1", "Autoparts!A:AC"); IMPORTRANGE("URL2", "Autoparts!A1:AC15000"); IMPORTRANGE("URL2", "Autoparts!A15000:AC30000"); IMPORTRANGE("URL3", "Autoparts!A:AC")}, {5, 29, 10}, ))) Do I need to add 0 at the end to bring the exact value. – HY JAPAN Oct 25 '22 at 03:43
  • @HYJAPAN you can add 0 if you wish but that has no effect on the logic. also if you vlookup just one cell C4 the output must be only one row with 3 cells. can you share a copy / sample of your sheet with an example of the desired output – player0 Oct 25 '22 at 06:52
  • please take a look @player0 FIXED and NSP are the importranges sheets FIXED PARTS https://docs.google.com/spreadsheets/d/1aR5NlrhgjPDkaoOEhGK83Hk3jdpWZ5HTcH7I6g85Vdg/edit?usp=sharing --- NSP PARTS https://docs.google.com/spreadsheets/d/1D8n_i-HXqgmt54OOVYpu6Y57cXbqUbV0E9bA2PyAM6Q/edit?usp=sharing --- TARGET SHEET https://docs.google.com/spreadsheets/d/1ZIe1gr0Jf_l4fBHRwW9AXEKdvQLPWZ-kNgMly76036w/edit#gid=0 – HY JAPAN Oct 26 '22 at 01:38