VLOOKUP DATA

[sumif and]

Worksheet 1: Grid!F10 = 4
Grid!F3 = 10 (1,000 units / 100 increments)

Worksheet 2: Formula in Cell =VLOOKUP(GRID!F10,mid_lcov,(GRID!F3+1)+1,1)

mid_lcov (range name) =
# of units
cover 1000 1100 1200
1 451.00 476.00 501.00
2 549.00 574.00 600.00
3 734.00 759.00 784.00
4 901.00 927.00 954.00

With it as is, it returns $1,142.00 which is the cost for 1,900 units. I
need it to pull the cost for 1,000 units using a 4-color cover which is
$901.00, but then go over one more row to compensate for cost variances. I
truly need it to pull $927.00 overall. But, can't get it there. Can anyone
help? Thank you so much!!!!

 

[Lars-Åke Aspelin]

Worksheet 1: Grid!F10 = 4
Grid!F3 = 10 (1,000 units / 100 increments)

Worksheet 2: Formula in Cell =VLOOKUP(GRID!F10,mid_lcov,(GRID!F3+1)+1,1)

mid_lcov (range name) =
# of units
cover 1000 1100 1200
1 451.00 476.00 501.00
2 549.00 574.00 600.00
3 734.00 759.00 784.00
4 901.00 927.00 954.00

With it as is, it returns $1,142.00 which is the cost for 1,900 units. I
need it to pull the cost for 1,000 units using a 4-color cover which is
$901.00, but then go over one more row to compensate for cost variances. I
truly need it to pull $927.00 overall. But, can't get it there. Can anyone
help? Thank you so much!!!!

The third argument of the VLOOKUP function tells which column in the
range that contains the wanted data.
In this case F3 holds 10 and 10+1 is 11 so you will get the data from
the correct row but from the 11'th column rather than from the 3rd
column as you would like.
Try replacing the +1 with -8 and you will have the correct data.

Hope this helps / Lars-Åke