How to Look up and then transfer information from tab to tab

New member, hopefully with an easy problem.

I have two tabs in an excel spreadsheet, one tab is Quotes, the other is Orders.

We will have multiple quotes for the same part number in the first tab. Their also will be multiple prices quoted for different order quantities. In the order tab we will have one line for each part number.

What I would like to do is to transfer the piece price and tooling cost from the quote tab to the order tab. When in the order tab I populate the Part Number, Order quantity, Vendor and Process in the Order tab, and have the piece price and tooling auto populate.

A very simplified Excel document is attached that hopefully shows what I'm asking. Any other questions, please let me know.

I initially thought VLOOKUP would be useful, but that is limited to being defined by one column.

Thanks.

In B4 of Quotes, copied down
=D4&E4&F4&G4
Then in Orders, H7 copied down

Formula:

Please Login or Register  to view this content.

In I7 copied down

Formula:

Please Login or Register  to view this content.

Note: this formula will give the 2 piece price for 1 -4 pieces, the 5 piece price for 5-9 pieces and the 10 piece price for anything 10 or above.

Hi,

In G7:

=INDEX(Quotes!F$4:F$8,MATCH(1,(Orders!$B7=Quotes!$B$4:$B$8)*(Orders!$C7=Quotes!$C$4:$C$8)*(Orders!$D7=Quotes!$D$4:$D$8)*(Orders!$E7=Quotes!$E$4:$E$8),0))

CTRL+SHIFT+ENTER to make it an array drag across to column J and down

In K7:

=IF($F7<5,$F7*H7,IF(AND($F7>=5,$F7<10),$F7*I7,IF($F7>=10,$F7*J7)))

drag down

I included the prices also on the sheet to show the available discounts when ordering quantities.

I hope this helps you