This is a VLOOKUP or maybe a IF(AND(...... Question!

[Jay Walsh]

Afternoon All,

I have a table in sheet 1 that lists 25 "Routes" in column A and columns B to E have Grid References "Max Latitude"; "Min Latitude"; "Max Longtitude"; "Min Longtitude" respectively. In my second sheet I have a list of 30000 entries each with a Latitude and Lontitude grid reference. My aim is to put in a formula that says if the grid reference is between the parameters laid out in sheet 1 then to display the "Route" number. I have tried the following:

IF(AND(G2<=Sheet1!$B$2:$B$28,G2>=Sheet1!$C$2:$C$28,H2<=Sheet1!$D$2:$D$28,H2>=Sheet1!$E$2:$E$28),Sheet1!$A$2:$A$28,"Error")

But this doesn't want to work. Does anyone have any ideas?

Cheers

Jay

 

[carlmack]

You could try this

=INDIRECT("'sheet1'!a"&SUMPRODUCT(--((Sheet1!$B$2:$B$28)<=G2),--((Sheet1!$C$2:$C$28)>=G2),--((Sheet1!$D$2:$D$28)<=H2),--((Sheet1!$E$2:$E$28)>=H2),--ROW(Sheet1!$A$2:$A$28)))

in sheet2 I2 and copy down.

 

[Travis]

it would help to see some of your data, but if its how I picture then you could use a sumproduct for this. See my example below.
In D17 =SUMPRODUCT((B17>=$B$3:$B$12)*(B17<=$C$3:$C$12)*(C17>=$D$3:$D$12)*(C17<=$E$3:$E$12)*A3:A12)

Book2
	A	B	C	D	E
1	Sheet 1
2	Route	Min Latitude	Max Latitude	Min Longtitude	Max Longtitude
3	1	10	15	60	65
4	2	15	20	65	70
5	3	20	25	70	75
6	4	25	30	75	80
7	5	30	35	80	85
8	6	35	40	85	90
9	7	40	45	90	95
10	8	45	50	95	100
11	9	50	55	100	105
12	10	55	60	105	110
13
14
15	Sheet 2
16	Entry	Latitude	Longitude	Route
17	1	22	73	3
18	2	36	88	7
Sheet1

 

[Jay Walsh]

Cheers guys, I put them both in, and with a bit of tinkering, they both gave the results I was looking for. Saved me a hard days work!!!

Cheers