How to get data from other sheet from the rows with Id matching the master sheet?

Question

"Data1" & "Data2" is for input the data

"Master" is for overview all of the data by input the ID and add a mark.

"Output1" & 2" is same as "Data1 & 2" but only show the items with mark in "Master"

Should I use vlookup or index and match?

Adding a link to a sample spreadsheet is great to supplement the information in your question, but it shouldn't replace the information in your question. When that link no longer works the essential information in this question will be lost and not useful to anyone. Please include the essential sample data in your question. Questions should be self-contained; links and images should only help add information, but shouldn't be required to understand the issue. — ale, Dec 29, 2016 at 13:33

user135384user135384 · Accepted Answer · 2016-12-29 06:57:53Z

I suggest vlookup in combination with filter. Here is an example:

=arrayformula(vlookup(filter(Master!D2:D, Master!E2:E="o"), {Data1!B:B, Data1!A:E}, {2,3,4,5,6}, false))

Step by step:

filter(Master!D2:D, Master!E2:E="o") takes the Id s from Master column D where column E is marked "o".
{Data1!B:B, Data1!A:E} prepares an array for lookup. This is only needed because your Id is not in the first column of the range; so an array has to be created with Id in the first column.
vlookup searches for the Ids returned by filter and returns the corresponding columns A-E from Data1
arrayformula ensures this all happens at once, no need to paste the formula row by row.

1 Answer 1