Complex lookup task for a newbies

[nhwong]

Hi i have somedata that looks like this:

TrackerID Date Time Day Northing_Y Easting_X Speed_kmh
102 7/2/2004 23:00:27 Friday 3.133891782 101.6948749 50
102 7/2/2004 23:00:34 Friday 3.133070734 101.6943788 53
102 7/2/2004 23:00:41 Friday 3.132447929 101.6935176 64
102 7/2/2004 23:00:47 Friday 3.132242237 101.6924949 64
102 7/2/2004 23:00:54 Friday 3.131492808 101.6917019 61
102 7/2/2004 23:01:00 Friday 3.130511904 101.691408 68
102 7/2/2004 23:01:06 Friday 3.129463391 101.6911232 71
102 7/2/2004 23:01:12 Friday 3.12857015 101.690505 71
102 7/2/2004 23:01:18 Friday 3.127903227 101.6895991 71
102 7/2/2004 23:01:24 Friday 3.127185884 101.6887128 75
102 7/2/2004 23:01:30 Friday 3.126517815 101.6877932 71
102 7/2/2004 23:01:36 Friday 3.126013613 101.6867848 75

if the value of Northing_Y AND/OR Easting_X of any row matched the
criteria of the following table, that row is said to be belong to that
criteria and an ID of that criteria should be assign next to that row.

Criteria yLB yUB xLB xUB
*1 >=3.137263 <3.138149
2 >=3.136371 <3.137263
3 >=3.135472 <3.136371
4 >=3.134575 <3.135472
5 >=3.133716 <3.134575
6 >=3.13297 <3.133716
*7 <=3.13297 >=101.693463
8 >=101.692597 <101.693463
9 >=3.131821 <101.692597
10 >=3.13099 <3.131821
11 >=3.130125 <3.13099
12 >=3.129286 <3.130125

For example if a row's Northing_Y is >=3.137263 AND <3.138149, then
it's ID is 1
If a row's Northing_Y is <=3.13297 AND it's Easting_X is >=101.693463,
then it's ID is 7. And so on...

I have get rid of the >= and <= from the criteria. However, i still
dont understand how to use the vlookup function to achieve what i need
after looking at the help from MSExcel. It's quite complicated to use
vlookup function in this situation as i have no experience in using
that function.

Can anyone help me on this issues? Thanks very much. (please forgive
my bad english..)

 

[ehntd]

Rather than using the VLookup function you need IF statements. Th
problem is that EXcel has a maximum of 7 if statements, so you woul
need 2 cells with the formula. Another thing that you could do i
write a macro for it and simply run it from your program, but that is
little more advanced. Try using IF´s and see if that works

 

[Avner]

I think you can try sorting one column of criteria and find the item'
position with lookup and references functions.

creteria yLb ......
1 3.136371
2 3.135472
3 3.134575
4 3.133716

I hope it helps yo

 

[changeable]

Thanks for the reply. I still dont understand how can i make it
However, i tried a vba (i just learned it, and still learning) wit
code like this:

Code
-------------------
Sub ID()
Dim i As Integer
Dim j As Integer
Dim coordinate As Variant
Dim criteria As Variant
Set coordinate = Range("E2:F131")
Set criteria = Range("J2:N128")
For i = 1 To 130
For j = 1 To 127
If coordinate(i, 1) = criteria(j, 2) And coordinate(i, 1) = criteria(j, 3) Then
Cells(i, 9).Value = criteria(j, 1)
ElseIf coordinate(i, 1) = criteria(j, 2) And coordinate(i, 2) = criteria(j, 5) Then
Cells(i, 9).Value = criteria(j, 1)
ElseIf coordinate(i, 1) = criteria(j, 3) And coordinate(i, 2) = criteria(j, 4) Then
Cells(i, 9).Value = criteria(j, 1)
ElseIf coordinate(i, 2) = criteria(j, 4) And coordinate(i, 2) = criteria(j, 5) Then
Cells(i, 9).Value = criteria(j, 1)
End If
Next j
Next i

End Su

 

[Avner]

The 4th argument of the function is range_lookup
Range_lookup is a logical value that specifies whether you want
VLOOKUP to find an exact match or an approximate match. If TRUE or
omitted, an approximate match is returned. In other words, if an exact
match is not found, the *next largest value that is less than
lookup_value * is returned. If FALSE, VLOOKUP will find an exact match.
If one is not found, the error value #N/A is returned.

If the list is sorted you can find the right criteria with a
combination of true and false values.

 

[JulieD]

Hi

this is very similar to a quesiton i asked the other day (and received help
from Domenic & Bob Phillips amongst others) to solve

here i was looking up a value in A1 (same as your Northing_Y) column in a
table in A8:C10 (same as your criteria table) and returning the value to
another cell

=IF(ISNA(INDEX($A$8:$A$10,MATCH(1,(A1>=$B$8:$B$10)*(A1<=$C$8:$C$10),0))),"No
match",INDEX($A$8:$A$10,MATCH(1,(A1>=$B$8:$B$10)*(A1<=$C$8:$C$10),0)))

so if you replace A1 with the first cell reference of the Northing_Y
and replace A8:A10 with the criteria column, B8:B10 with the minimum values
and C8:C10 with the maximum values and as its an ARRAY formula you need to
enter it using CTRL & SHIFT & ENTER (not just enter) it should work for you.
You can then copy it down the rest of the northings you want to look up.

Hope this helps
Cheers
JulieD