Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
How to lookup data in a row and column | Excel Discussion (Misc queries) | |||
complex lookup | Excel Discussion (Misc queries) | |||
Another Lookup function, please | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |