here is the sample of my sheet: https://docs.google.com/spreadsheets/d/1gr2QHtDCBHiOXUAiQ1ZVEmDKq5CGIGkLGpRM2tZhPBs/edit?usp=sharing

"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
    Commented Dec 29, 2016 at 13:33

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.