-2

We have this table

+--------------+-----------+----------+-------+
| Destination  | Beginning |   End    | Value |
+--------------+-----------+----------+-------+
| Riohacha     | 0 kg      | 500 kg   | $100  |
| Riohacha     | 501 kg    | 1000 kg  | $200  |
| Manaure      | 0 kg      | 1000 kg  | $300  |
| Manaura      | 1001 kg   | 2000 kg  | $400  |
| Maicao       | 0 kg      | 1000 kg  | $500  |
| Maicao       | 1001 kg   | 2000 kg  | $600  |
+--------------+-----------+----------+-------+

I need a formula that

  • when "Cell A1: Riohacha" and "Cell B1: 478 kg", it returns the "Value" $100, because the destination matches Riohacha on the table and the weight in kg is between 0 kg and 500 kg.
  • when "Cell A1: Manaure" and "Cell B1: 1030 kg", it returns the "Value" $400 because it finds Manaure on the table and because the weight 1030 kg is between 1001 kg and 2000 kg.

In short I need a formula in "Value" where it finds the number that has the same destination and is between the numbers.

Tunaki
  • 132,869
  • 46
  • 340
  • 423
Luis Amaris
  • 3
  • 1
  • Welcome to SO. Please show us how you have tried to solve the problem, and what issues you have run into. This is not a free code-writing service but exists to help others with code or functions they are trying to develop. As such, we expect to see clear cut questions with examples of data, attempted code, actual output, desired output, research efforts to solve the problem, etc. Please read the HELP pages for information as to [How to Ask a Good Question](http://stackoverflow.com/help/how-to-ask); and also [How to Provide an Example](http://stackoverflow.com/help/mcve) – Ron Rosenfeld Aug 30 '16 at 19:06
  • See [Two column lookup in table array using INDEX and MATCH](http://stackoverflow.com/questions/33010190/two-column-lookup-in-table-array-using-index-and-match/33011266#33011266). –  Aug 30 '16 at 19:08

1 Answers1

0

I put my table in A12:D18 and used this array formula:

=INDEX($D$13:$D$18,MATCH(B1,IF($A$13:$A$18=A1,--LEFT($B$13:$B$18,LEN($B$13:$B$18)-2))))

being an array formula it needs to be confirmed with Ctrl-Shift-Enter when exiting edit mode. If done correctly Excel will put {} around the formula.

![enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81