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.

	A	B	C	D	E
1	Unit Start	Unit End	Account Start	Account End	Result
2
3		49	4001	4500	Manufacturing Rent
4	50	54	4001	4500	Internet Rent
5	55	899	4001	4500	Retail Rent
6	900	999	4001	4500	Admin Rent
7		49	4501	5000	Manufacturing Rates
8	50	54	4501	5000	Internet Rates
9	55	899	4501	5000	Retail Rates
10	900	999	4501	5000	Admin Rates
11
12
13	Unit		Account		Look Up
14	52		4230		Internet Rent
15	720		4750		Retail Rates

<TBODY>
</TBODY>

 

[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.

	A	B	C	D	E
1	Unit Start	Unit End	Account Start	Account End	Result
2
3		49	4001	4500	Manufacturing Rent
4	50	54	4001	4500	Internet Rent
5	55	899	4001	4500	Retail Rent
6	900	999	4001	4500	Admin Rent
7		49	4501	5000	Manufacturing Rates
8	50	54	4501	5000	Internet Rates
9	55	899	4501	5000	Retail Rates
10	900	999	4501	5000	Admin Rates
11
12
13	Unit		Account		Look Up
14	52		4230		Internet Rent
15	720		4750		Retail Rates

<tbody>
</tbody>

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