Comparing items and calculating prices

[drewship]

Hello all,
I thought I was beginning to understand some of the functions and formulas but I am still at a loss.

I have attached a sample spreadsheet for reference.

What I am trying to do is eliminate column 'J' on the Distribution tab. I want to use column 'C' on the Totals tab to calculate the subtotal based on matching the products on the Distribution tab in column 'E' with the Products list on the Totals tab in column 'A'. Once the match is made, I need to multiply the associated price in column 'C' on the Totals tab and the associated quantity in column 'F' on the Distribution tab with the result going in column 'K' on the Distribution tab.

I tried

=VLOOKUP(E:E,Totals!$A:$A,3,TRUE)*F:F

and

=SUMPRODUCT(SUMIF(E3,Totals!A3,F3)*Totals!C3)

but neither works and I do not know what else to try. Any help would be appreciated.

Thanks in advance,
Andrew

[NBVC]

Try:

=VLOOKUP(E3,Totals!$A$3:$E$18,3,FALSE)*F3

copied down

[DonkeyOte]

Do you mean

K3: =F3*VLOOKUP(E3,Totals!A:C,3,0)

[drewship]

Thanks NBVC!!! I thought after matching the product in 'A' on the Totals tab, it would automatically associate it with the correct price. Now I see (and can hopefully remember) that all the cells in the list have to be defined to make the correct association.

In the formula, I tried 'True' and you used 'False' for the range_lookup. Can you explain what this actually doing?

Thanks again,
Andrew

[drewship]

Thanks DonkeyOte. Your formula makes it easy to add data to the lookup list without changing the code!!

Andrew

[drewship]

Thanks NBVC. 'False' looks like the best way to go for anything I need to do.

Andrew

[drewship]

Another question for you. I need the calculation to perpetuate down the entire column. Cells that cannot be calculated yet (because the source cells are blank) display #N/A. I tried conditional formatting to format the cell to use white text to hide the #N/A but that does not work. I looked on the Options menu but did not see anything that would hide these. Is there a way to hide #N/A with VBA?

Thanks
Andrew

[NBVC]

You should have a look at the Excel Help files for the Vlookup function... it explains the difference there well.

Basically, the False makes Vlookup look for exact matches no matter what sort order you lookup table is in... the True depends on the first column in the lookup table being sorted in Ascending order, then it looks for the closest match.. and returns the corresponding item.