Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
nhwong
 
Posts: n/a
Default Complex lookup task for a newbies

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..)
  #2   Report Post  
ehntd
 
Posts: n/a
Default


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


--
ehntd
------------------------------------------------------------------------
ehntd's Profile: http://www.excelforum.com/member.php...o&userid=15865
View this thread: http://www.excelforum.com/showthread...hreadid=276503

  #3   Report Post  
Avner
 
Posts: n/a
Default


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

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

I hope it helps you


--
Avner
------------------------------------------------------------------------
Avner's Profile: http://www.excelforum.com/member.php...fo&userid=5078
View this thread: http://www.excelforum.com/showthread...hreadid=276503

  #4   Report Post  
changeable
 
Posts: n/a
Default


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) with
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 Sub
--------------------

the code is written based on the following picture. But i dont get any
results. What's wrong here?
[image: http://img.photobucket.com/albums/v5...eable/vba.jpg]


--
changeable
------------------------------------------------------------------------
changeable's Profile: http://www.excelforum.com/member.php...o&userid=15714
View this thread: http://www.excelforum.com/showthread...hreadid=276503

  #5   Report Post  
Avner
 
Posts: n/a
Default


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.


--
Avner
------------------------------------------------------------------------
Avner's Profile: http://www.excelforum.com/member.php...fo&userid=5078
View this thread: http://www.excelforum.com/showthread...hreadid=276503



  #6   Report Post  
JulieD
 
Posts: n/a
Default

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


"nhwong" wrote in message
om...
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..)



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I lookup and return different values when the lookup value. kg Excel Discussion (Misc queries) 1 January 20th 05 01:53 AM
How to lookup data in a row and column Confused Excel Discussion (Misc queries) 5 January 10th 05 09:20 PM
complex lookup [email protected] Excel Discussion (Misc queries) 1 December 17th 04 03:01 PM
Another Lookup function, please Butch Excel Worksheet Functions 3 November 3rd 04 09:52 AM
double lookup, nest, or macro? Josef.angel Excel Worksheet Functions 1 October 29th 04 09:50 AM


All times are GMT +1. The time now is 06:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"