I have two tabs in Google Sheet.

Tab 1: Data, which contains all row data.

Tab 2: Output tab, where I need the results to appear.

The data is about products and their components. Each product has 5 components. Column A of both tabs is a unique code associated with the product. In the Data tab, each product has 5 rows, one for each component. Each component has a unique code in column B.

I need to be able to list in the output tab all the products with their unique codes and each of their 5 components codes. The lookup key value is the product code in column A.

I usually use vlookup or index/match formula but in this case, it finds multiple results for the value in column A and so returns only one of the components codes in column B.

How can I tell the formula to find each instance of the code in column A and return all of the values from column B?

  • Welcome. =query(A2:B) - assumes that there is a header row in the Data Tab. If the Data has multiple instances of products then: =unique(query(A2:B)) – Tedinoz May 6 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.