I am trying to build a document which will make it easier for my staff to make 'Email Structure' essentially with a series of dropdown choices and when they select an option, different information populates the cells from another sheet within the document. The most important thing is that it brings in the product name and the description of the product across all four columns (the product name, the price, the product type and the product description).

I have used StackOverflow research to try a few different combinations so far but not much has worked.

You can see what I am attempting by seeing the sheet 'Email Structure' on the following Google Sheet: https://docs.google.com/spreadsheets/d/1ExQGxxesv64OZJc4uoHVMLkaOCFUVsIQWVBPMuOB8xE/edit?usp=sharing

I am eventually going to have thousands of products across different sheets to pick from, so any advice on this would be really great so I can learn and understand how to do this before cascading it to apply across many more areas.

Options I have tried in cell C4 of sheet 'Email Structure':

=VLOOKUP(B4, All Products!B6:C65, 2,FALSE)

=IF(B4="Gaming Energy",VLOOKUP(B4,All Products!B6:B12,2,FALSE),IF(B4="Pro Series",VLOOKUP(B4,All Products!B27:B36,2,FALSE),VLOOKUP(B4,All Products!B51:B65,2,FALSE)))

=IF(B4="Gaming Energy",VLOOKUP(B4,A6:B12,2,FALSE),IF(B4="Pro Series",VLOOKUP(B4,A27:B36,2,FALSE),VLOOKUP(B4,A51:B65,2,FALSE)))

=QUERY('Gaming Energy'!B6:E12,"select B,C,D,E where 'Product'='"&B4&"'")

CodePudding user response：

Are you going to have all products in one sheet and then distribute them in the others based on filters??

If you have everything in 'All Products', you can do a Query similar to your last one:

=QUERY('All Products'!B:E,"WHERE C = '"&B4&"'")