Excel lookup riddle

[MrMartiens]

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?

Thanks a lot in advance!

 

[gsbelbin]

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)

 

[Andrew Poulsom]

An alternative:


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

Formula edited.

 

[Ormus]

NM, above solutions work

 

[MrMartiens]

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!

 

[gsbelbin]

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

 

[MrMartiens]

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

 

[gsbelbin]

Glad to help and welcome to the board!

 

[Aladin Akyurek]

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?

Thanks a lot in advance!

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

Control+shift+enter, not just enter...

=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))