Index and Match formula combination

**anstasija** · 10-20-2011, 08:43 PM

Hi Everyone,

I have been trying to use some suggestions in similar posts today for coulpe hours now and I just cannot get it to work.

I have two files. I would like to include the price from the "test price" file in the "test complete" file. The price is depending on customer number and the part number, so two matches are requried.

I believe it does not like to work with those two separate files or I am selecting the array not correctly.

Could you please help me to create the formula in the "price" column (D) in "test complete" file from the "test price" (column C) file?

Many thanks!

**khamilton** · 10-20-2011, 10:20 PM

enter this with control + shift + enter.

Please Login or Register  to view this content.

**anstasija** · 10-21-2011, 08:09 AM

Originally Posted by khamilton

enter this with control + shift + enter.

Please Login or Register  to view this content.

Thank you! I have done so but it returns: #VALUE - maybe I have done it the incorrect way. I have copied the formula and tried to paste it using: ctrl+shift+enter. It has not pasted anything....

**NBVC** · 10-21-2011, 08:17 AM

How about?

=SUMPRODUCT(--('[Test price.xlsx]Prices'!$A$2:$A$7=A2),--('[Test price.xlsx]Prices'!$B$2:$B$7=B2),'[Test price.xlsx]Prices'!$C$2:$C$7)

this pulls the corresponding price, you can then further multiply by Quantity.