Google Sheet : Importing Data Based on Cell "All City"

[Jhon Mustofa]

hi all..

how to pull data using Importrange from multiple sheet into one sheet with criteria like this
i have 3 sheet ,my target in sheet "my expected" .
how to pulling data with criteria name cell "All City" from Col."Target" and Col "Actually"
please, check this share link
https://docs.google.com/spreadsheets...it?usp=sharing

any help. thank you so much

john m

[Haluk]

In sheet "my expected", clear your range D2:E4 and enter this in cell D2 to fetch the data from sheet "data1";

Please Login or Register  to view this content.

Similarly, enter this in cell D3 to fetch the data from sheet "data2";

Please Login or Register  to view this content.

and so on ....

I've entered these formulas in range G2:G4 for you...


Edit:

As an alternative, you can use the following formula to fetch the data from sheet "data1" (entered in your sheet as well)

Please Login or Register  to view this content.

[Jhon Mustofa]

hi Haluk , you always helping, thank you so much..
but i have 2 question :

Please Login or Register  to view this content.

1. for number in red font, where come from 6;10?
2. like my example, sorry i miss it, actually i want all data can be pulled e.g. pulled data in col. b2-b4, c2-c4, d2-d4, e2-e4...it'means my data in col.b,col c,col d,col e, it's all given manually. i don't want it.
3. what if the code work from another sheet? like importrange..

[Haluk]

Jhon;

1) In the ArrayFormula version of Vlookup approach, the data we are trying to fetch is in the 6th and 10th columns of the range B6:K## on sheet "data1"

2) In order to get all the data of the result table on sheet "my expected", for sheet "data1" use this formula;

Please Login or Register  to view this content.

and so on... for the other sheets.

Anyway, i have entered the formulas in range I9:I11 on your tab "my expect"

3) If you are on another sheet and trying to fetch data from the sheet that you've provided the link in your message #1, then on the other new sheet, you can use this formula to fetch the data from the tab named as "data1" in the link.

Please Login or Register  to view this content.

As you know, "https://docs.google.com/spreadsheets/d/14CKwi0hk6Vj........" part of the IMPORTRANGE formula, is the URL to the sheet where we fetch the data. We get the results, by querying the imported range.

For the tab "data2" the formula is similar;

Please Login or Register  to view this content.

and, for tab "data3";

Please Login or Register  to view this content.

[Jhon Mustofa]

hi Haluk, very clear,....Thank you so much!!!!

[Haluk]

You are welcome,

BTW, if you want to get the headers "Sales A", "Sales B" .... also, you can use the modified formula given in my message #4 as;

Please Login or Register  to view this content.

and the others, similarly .... where i have entered these formulas in range G13:G15 for your reference.