# Excel lookup riddle

#### MrMartiens

##### New Member
Good day all,

I am facing a Ecxel lookup challenge which is keeping me awake. Hopefully someone will be able to give me back my nigh rest

Here is the situation:

I have a lists with shipments, for which I know which carrier transported it and I know what the weight was. All I need in addition is the rate for this shipment.

In a second file I have a list with rates per carrier, per (so-called) weight breaks.

I now need to do a lookup where the shipment's carrier matches, and the shipment's weight falls withing a certain weight range, and then return the rate.

In the example below I have tried to clarify it.

I will need to perform a lookup for cell C3, which will return the value 280, as the carrier XYZ matches, and the weight (800) falls within the specified weight break (600-1000).

Does anybody know how I can create a formula which will help me get this value returned?

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
This is a job for good old SUMPRODUCT. In C3 try this

=SUMPRODUCT(--(\$A\$11:\$A\$14=A3), --(\$B\$11:\$B\$14<=B3), --(\$C\$11:\$C\$14>=B3), \$D\$11:\$D\$14)

Last edited:
An alternative:

=INDEX(D11:D14,MATCH(1,INDEX((A11:A14=A4)*(B4>=B11:B14)*(B4 < C11:C14),),FALSE))

Formula edited.

Last edited:
NM, above solutions work

Last edited:
Good day!

Thanks for your quick reply! It's a relief to see that there will be a solution for this. I copy-pasted your formula in C3, however, I'm getting "0" as a result.

Do I need to modify the formula in any way before it will work?

Thanks a lot for your support!

Yes it was a mistake in my original post. I've since edited it, try the new version

Thank you so much guys! You just made my day!!!

Glad to help and welcome to the board!

Good day all,

I am facing a Ecxel lookup challenge which is keeping me awake. Hopefully someone will be able to give me back my nigh rest

Here is the situation:

I have a lists with shipments, for which I know which carrier transported it and I know what the weight was. All I need in addition is the rate for this shipment.

In a second file I have a list with rates per carrier, per (so-called) weight breaks.

I now need to do a lookup where the shipment's carrier matches, and the shipment's weight falls withing a certain weight range, and then return the rate.

In the example below I have tried to clarify it.

I will need to perform a lookup for cell C3, which will return the value 280, as the carrier XYZ matches, and the weight (800) falls within the specified weight break (600-1000).

Does anybody know how I can create a formula which will help me get this value returned?

Andrew's suggestion is victimized by the html parser... Here is something that is equivalent to it:

Control+shift+enter, not just enter...
Rich (BB code):
``````=INDEX(\$D\$11:\$D\$14,MATCH(1,IF(\$A\$11:\$A\$14=A3,
IF(B3 >= \$B\$11:\$B\$14,IF(B3 < \$C\$11:\$C\$14,1))),0))``````

Replies
5
Views
125
Replies
3
Views
176
Replies
2
Views
1K
Replies
3
Views
701
Replies
7
Views
239

1,196,073
Messages
6,013,264
Members
441,758
Latest member
Abren

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back