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

Jay Walsh

Board Regular
Joined
Jan 19, 2004
Messages
117
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

carlmack

New Member
Joined
Feb 11, 2005
Messages
39
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

Well-known Member
Joined
Feb 26, 2002
Messages
1,711
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
ABCDE
1Sheet 1
2RouteMin LatitudeMax LatitudeMin LongtitudeMax Longtitude
3110156065
4215206570
5320257075
6425307580
7530358085
8635408590
9740459095
108455095100
1195055100105
12105560105110
13
14
15Sheet 2
16EntryLatitudeLongitudeRoute
17122733
18236887
Sheet1
 

Jay Walsh

Board Regular
Joined
Jan 19, 2004
Messages
117
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,573
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top