index or sumproduct, maybe

[BrutalLogiC]

Hey I hope someone can help

I have two sheets in my workbook
Sheet 1 with column called Services (cells H15:H28000) and column called quantities (cells J15:J28000)
Sheet 2 with column called Services (cells B4:B150) and Rates (cells C4:C150)

I'm trying to put a formula in sheet1 that will look at the long list of services and calculate the cost which would be the rate (from sheet 2 for that service) multiplied by the corresponding rate from sheet2.

Hopefully that makes sense..

 

[Aladin Akyurek]

Care to post heavily scaled-down samples along with the expected results for those samples?

 

[BrutalLogiC]

Care to post heavily scaled-down samples along with the expected results for those samples?

something like
Sheet1 H15 = Electrical (service)
Sheet1 J15 = 10 (quantity)
Sheet2 B4 = Electrical (service again but without any duplicates, just 100 odd rows)
Sheet2 C4 = 30 (rate)

Sheet 1 W15 is the cell I'm trying to populate, the end result should be 300. So it's looking at the service in sheet1 H15 then multiplying the corresponding rate for that service in sheet2 C4 by the quantity in sheet1

urgh not sure if that helps or not??

 

[Aladin Akyurek]

Looks like we can enter the following formula in W15 and copy down:

=$J15*VLOOKUP($H15,Sheet2!$B$4:$C$150,2,0)

Note that this is a per record computation.