Array formula with vlookup and importrange to get specific values in google sheets-CodePudding

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.

CodePudding user response：

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))

CodePudding user response：

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")