Cell lookup - based on range and two other cells

[dblue42]

Hello,

Newbie here. I am trying to automate the processing of survey data, to make life quicker and easier. The surveying trolley takes measurements at regular intervals, and logs these alongside the distance travelled (either KM/Metres or Miles/Yards). Some of the locations along the route have a site name, with a known distance limits (e.g. Site 1 is from 30.980km to 30.990km).

What I want is either a formula or a script that fills in the location names automatically, so for example cells C2:C3 both have "Site 1" filled in for me.

I have found a similar example of this on the forum, but can't get my head around the code to make this work for me. Can anyone help please?

Thanks.

 

[Peter_SSs]

so for example cells C2:C3 both have "Site 1" filled in for me.

.. and what would C4 have, given that 31.0 does not come in any of the ranges in the lookup table?
Similar for C7 & C8?

 

[dblue42]

.. and what would C4 have, given that 31.0 does not come in any of the ranges in the lookup table?
Similar for C7 & C8?

Good question. Not all locations have names; so C4 would normally be left blank.

This may seem a bit odd, but its the nature of the infrastructure I'm working with.

 

[Peter_SSs]

For the future, this would likely get you more response as helpers would not have to manually type out the sample data to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this could work for you.

22 02 14.xlsm
	A	B	C	D	E	F	G	H	I
1
2	30	980.5	Site 1
3	30	990	Site 1		Site 1	30	980	30	990
4	31	0			Site 2	31	10	31	115
5	31	110	Site 2		Site 3	31	220	31	250
6	31	115	Site 2
7	31	120
8	31	200
9	31	220.5	Site 3
10	31	230	Site 3
Lookup

Cell Formulas
Range	Formula
C2:C10	C2	=IF(SUMPRODUCT(--(A2>=F$3:F$5),--(B2>=G$3:G$5),--(A2<=H$3:H$5),--(B2<=I$3:I$5))=0,"",INDEX(E$3:E$5,SUMPRODUCT(--(A2>=F$3:F$5),--(B2>=G$3:G$5),--(A2<=H$3:H$5),--(B2<=I$3:I$5),ROW(E$3:E$5)-ROW(E$3)+1)))

 

[dblue42]

Thanks Peter, that has worked perfectly. I'll remember your advice about posting rules for next time.

 

[Peter_SSs]

Thanks Peter, that has worked perfectly.

You're welcome. Thanks for the confirmation.

I'll remember your advice about posting rules for next time.

It isn't a rule that you have to use XL2BB, just advice about the best way to get more people to consider answering your question(s).