Offset And Vlookup Combo Help!!

[kollizion]

HI, Ive been struggling with this for a while, so i need some help. I
has to be a simple answer that i cant seem to be grasping, so call m
retarted. Ok, here goes.....

I have an array on a worksheet that is formatted like so....

day rate price
75 4.500 95.125
75 4.625 96.254
75 4.750 96.897
60 4.250 97.587
60 4.375 98.131
60 4.500 99.005
45 4.250 96.158
45 4.375 97.574
45 4.500 98.586

Then on another worksheet i have some columns sorted like so....

col1 col2 col3 col4
Rate 45 60 75
4.250
4.375
4.500
4.625
4.750

Ok, i am trying to do a lookup for each rate, and then show th
corresponding price, but to lookup based on the # of days. I know
can do a VLOOKUP by using an array for each group of prices and thei
corresponding days, but im wondering if there is a way to combine th
OFFSET with the VLOOKUP with an IF function so that only return th
price IF its 45 and the rate is whatever is on that row.

Have i confused anyone yet?? haha

Please help!!

Thanks!

 

[Mangesh Yadav]

Consider your first table in range A1:C9 (without the headers, only values)

Consider you have 4.25, 4.375 in cells A13 downwards, and 45, 60, 75 from
B12 to the right, then use:
=SUMPRODUCT(--($B$1:$B$9=$A13),--($A$1:$A$9=B$12),$C$1:$C$9)
and copy to the full table.

Mangesh

 

[kollizion]

That worked perfect!!! Thanks so much!!

 

[Mangesh Yadav]

Thanks for the feedback.

Mangesh