Filter with IMPORTRANGE need data from 2 Sheets, whould i use query, vlookup, match?

user_12882020 0
Viewed 25

im trying to simplify my SS with 1 formula instead of what im doing in cols C, D and E with formulas in every row

basically on my example i get data to col B from Derivative sheet on source SS and need to get data to cols C, D and E from Instruments sheet

where Instruments!B:B = Derivatives!B:B - link between those 2 sheets

it should be something like INNER JOIN with SQL, so i tried with query, match, vlookup and couldnt do it, probably cos my lack of skills

Example

Source SS I import daily from brazilian stock exchance and overwrite its content, quite big

Source

end result i want would be one formula in C1, D1, and E1, as i have on other Cols

if help in col C i can adapt to the other cols easily

vlookup google-sheets import join arrays
2 Answers

delete everything in range C2:E and use this in C2:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B; {
 IMPORTRANGE(Config!B3; "Instruments!B:B")\
 IMPORTRANGE(Config!B3; "Instruments!K:K")\
 IMPORTRANGE(Config!B3; "Instruments!T:T")\
 IMPORTRANGE(Config!B3; "Instruments!AJ:AJ")}; {2\ 3\ 4}; 0)))

enter image description here

user_56326290

Simplified example

What I understand is that you want to get VLOOKUP output from multiple ranges in one formula, one table and diffrent indexes or columns.

To simplify the answer of player0 so it can be used in general cases, lets streamline the problem in one sheet one tab without importrange instead we use cell references.

Paste this formula next to the search_key "State", Take a look at the Example Sheet.

=ARRAYFORMULA(IFNA(VLOOKUP(F4:F, {
 A4:D11;
 A14:D20;
 A24:D31}, {4, 3, 2}, 0)))

enter image description here

Explanation

  • VLOOKUP the search_key range F4:F in the stacked ranges of source tables { A4:D11; A14:D20; A24:D31} Meaning { table1; table2; table3}.

  • Set the VLOOKUP index to {4, 3, 2} In order to return multiple values, we used multiple indexs numbers ( columns 4, 3, 2 ) enclosed in curly brackets.

  • In the VLOOKUP formula, we have created an array using Curly Brackets {}. But by default, VLOOKUP is not an Array Formula, Therefore, an ARRAYFORMULA should be used to wrap the formula.

user_195296940
Related