Quote sheet referring to price list

[RoseH915]

Hello. I am trying to make a quote sheet. We have 300 products. I was hoping there was a way to autofill my quote sheet with Name, Price, Bundle Qty using some sort of reference tool.

So what I envision happening:

Sheet 2 has the products listed with Item #, Name, Price and Bundle for each product.

When I go to sheet 1 that has my template, I can enter the item # (or name or any sort of reference that would make this work) and Excel autofills the Name, Price and Bundle for that product into the appropriate cells on my template. Essentially "pulling" that data from sheet 2 to auto fill my cells.

Or I can pull from another workbook, or alternate columns within sheet 1 if need. Whatever Excel is capable of.

Is this possible?

[#2]

@RoseH915 That's done with one of several lookup functions available in Excel, depending on your version. VLOOKUP or INDEX combined with MATCH work in older versions. XLOOKUP works in the most recent versions of Excel (MS365 and 2021).

[#3]

@RoseH915 Yes, as the previous commenter mentioned, there are many options to accomplish your objective.  The new Dynamic Array formulas are amazing.  Here an example formula available in Office 365 which returns the array of details you mentioned in a horizontal or vertical fashion. 

This one in particular will return the name, price & bundle in a vertical layout.  To use a horizontal layout, just erase the "Transpose(" and the final ")".  

=TRANSPOSE(VSTACK(Table1[#Headers],FILTER(Table1,(Table1[Item'#]=Sheet3!B1))))

[#4]

Thank you for your help! Worked like a charm. @Riny_van_Eekelen