Look Up with Multiple Ranges

[bishio]

Apologies for almost certainly posting a question that has already been answered here, but I have tried a number of the solutions without any luck as I cannot relate the variables to my own, so here goes (note I am running Excel 2010 and I have no idea how to create the table below properly here);

I would like cells E14 and E15 to contain a formula based on the mapping table A3:E10 (Note my file has hundreds of these, this is an example). Columns A and B represent Range 1 that I would like cell A14 to be looked up or matched against, and column C and D represent Range 2 that I would like cell C14 to be matched against to deliver "Internet Rent" into cell E14.

Any help, preferably without Ctrl+Alt+Ent functions, would be gratefully received.

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Unit Start
[/TD]
[TD]Unit End
[/TD]
[TD]Account Start
[/TD]
[TD]Account End
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]49
[/TD]
[TD]4001
[/TD]
[TD]4500
[/TD]
[TD]Manufacturing Rent
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]50
[/TD]
[TD]54
[/TD]
[TD]4001
[/TD]
[TD]4500
[/TD]
[TD]Internet Rent
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55
[/TD]
[TD]899
[/TD]
[TD]4001
[/TD]
[TD]4500
[/TD]
[TD]Retail Rent
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]900
[/TD]
[TD]999
[/TD]
[TD]4001
[/TD]
[TD]4500
[/TD]
[TD]Admin Rent
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]49
[/TD]
[TD]4501
[/TD]
[TD]5000
[/TD]
[TD]Manufacturing Rates
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]50
[/TD]
[TD]54
[/TD]
[TD]4501
[/TD]
[TD]5000
[/TD]
[TD]Internet Rates
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55
[/TD]
[TD]899
[/TD]
[TD]4501
[/TD]
[TD]5000
[/TD]
[TD]Retail Rates
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]900
[/TD]
[TD]999
[/TD]
[TD]4501
[/TD]
[TD]5000
[/TD]
[TD]Admin Rates
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Unit
[/TD]
[TD][/TD]
[TD]Account
[/TD]
[TD][/TD]
[TD]Look Up
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]52
[/TD]
[TD][/TD]
[TD]4230
[/TD]
[TD][/TD]
[TD]Internet Rent
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]720
[/TD]
[TD][/TD]
[TD]4750
[/TD]
[TD][/TD]
[TD]Retail Rates
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Arithos]

Apologies for almost certainly posting a question that has already been answered here, but I have tried a number of the solutions without any luck as I cannot relate the variables to my own, so here goes (note I am running Excel 2010 and I have no idea how to create the table below properly here);

I would like cells E14 and E15 to contain a formula based on the mapping table A3:E10 (Note my file has hundreds of these, this is an example). Columns A and B represent Range 1 that I would like cell A14 to be looked up or matched against, and column C and D represent Range 2 that I would like cell C14 to be matched against to deliver "Internet Rent" into cell E14.

Any help, preferably without Ctrl+Alt+Ent functions, would be gratefully received.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Unit Start[/TD]
[TD]Unit End[/TD]
[TD]Account Start[/TD]
[TD]Account End[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]49[/TD]
[TD]4001[/TD]
[TD]4500[/TD]
[TD]Manufacturing Rent[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]50[/TD]
[TD]54[/TD]
[TD]4001[/TD]
[TD]4500[/TD]
[TD]Internet Rent[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]55[/TD]
[TD]899[/TD]
[TD]4001[/TD]
[TD]4500[/TD]
[TD]Retail Rent[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]900[/TD]
[TD]999[/TD]
[TD]4001[/TD]
[TD]4500[/TD]
[TD]Admin Rent[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]49[/TD]
[TD]4501[/TD]
[TD]5000[/TD]
[TD]Manufacturing Rates[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]50[/TD]
[TD]54[/TD]
[TD]4501[/TD]
[TD]5000[/TD]
[TD]Internet Rates[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]55[/TD]
[TD]899[/TD]
[TD]4501[/TD]
[TD]5000[/TD]
[TD]Retail Rates[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]900[/TD]
[TD]999[/TD]
[TD]4501[/TD]
[TD]5000[/TD]
[TD]Admin Rates[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Unit[/TD]
[TD][/TD]
[TD]Account[/TD]
[TD][/TD]
[TD]Look Up[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]52[/TD]
[TD][/TD]
[TD]4230[/TD]
[TD][/TD]
[TD]Internet Rent[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]720[/TD]
[TD][/TD]
[TD]4750[/TD]
[TD][/TD]
[TD]Retail Rates[/TD]
[/TR]
</tbody>[/TABLE]

Since you preferably dont want Arrays, it makes it harder,

Howerver, this formula would do it,
(This would go in cell E14)
And in column F, you need a "counter" from F3:F10, which is just from 1 to 8. <- Make this to make the formula work.

Then paste this in Cell E14, and drag down.
=INDEX(E3:E10;MATCH(SUMPRODUCT(--(A14>$A$3:$A$10)*--(A14<$B$3:$B$10)*--(C14>$C$3:$C$10)*--(C14<$D$3:$D$10)*$F$3:$F$10);$F$3:$F$10;0))

Note that the Red part is the "Counter" and, note, i'm using ";" as a formula divider. (or what you would call it)

The "counter" in column F might be avoidable, but I do not know how, and the SUMPRODUCT formula, does not recognise text, as its a matrix operating formula (to my knowledge)

 

[bishio]

Sturla,

Thank you very much for the formula, it worked perfectly once I replaced the semi colons with commas and changed the '<' and '>' to be '=<' and '>='

You saved me many headaches, and hours trying to figure it out myself, so thank you once again.

Simon (Bishio)

 

[Arithos]

Lovely to hear that