#1
|
|||
|
|||
Lookup a value from non-sorted data
Hi,
I'm a newbie in this forum and pls help me. I have 2 columns; A and B. What I want to do is look for a value in column A, based on reference value in a cell. When the value is found, i will then use vlookup to point the value in column B. Normally I would use lookup(lookup_value,array) to do this. If the data is sorted in an order (ascending or descending) it would be no problem BUT if data is non sorted (2 or 3 same data may appear at different row in the column), the formula will point me to the value at lower row of the column. The data is actually acquired from a machine and will be used to present a scattered graph. Is there any better way or specific formula to do this? Regards, Udea |
#2
|
||||
|
||||
Hi Udea,
For a lookup value in D1, try: =INDEX(B:B,MATCH(D1,A:A,0))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] Last edited by macropod; 04-24-2011 at 02:13 AM. Reason: Added Match 'type' to ensure correct matching |
#3
|
|||
|
|||
mr. macropod,
thanks a lot for your reply but i couldn't under stand how to use your formula in my spreadsheet. my bad for not attach the file before, so here is the attachment. thanks in advance. regards, udea |
#4
|
||||
|
||||
Hi Udea,
It appears the value you are trying to lookup (151) is not in the dataset. You did not say in your post that the data you were trying to match did not exist in the data. Also, the nearest match that is less than that is 149.8026, which is on row 58. I do not see why you would expect a match with the 142.9039 on row 7. You need to explain why the formula should match row 7 and not row 58. To match row 58, the formula for D2 is: =MAX(IF(A3:A122<=A1,A3:A122)) input as an array formula (Ctrl-Shift-Enter) and the corresponding value for E2 is found by: =INDEX(B3:B122,MATCH(D2,A3:A122,0))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Mr. Macropod,
As i explained before, the data is acquired from some type of test and then presented in a graph. Value 151 is actually =max(a3:a122)/2, which is at row 23. normally we would do it manually by drawing a line on the graph to get the x-value at the intersection of y-value (151). By looking at the data set, the nearest is the value in row 7. If i can point the data for y-value correctly, then i can calculate the x-value. I said row 7 is the nearest to the value 151 because the value is in the "middle" of row 7 and 8 and is before the peak (row 23). I attached the excel file with the graph for your better picture. Thank you very much for your effort. Regards, Udea |
#6
|
||||
|
||||
Actually, the result of that formula is 150.96515. However that makes little difference to the syntax of the formula required for matching purposes.
Quote:
You need to set out fully what the matching logic is.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Mr. Macropod,
You are right, the value is 150.96515, but i just need the round up number. The matching logic to find the value is that the value should be below the peak. from my point of view (for the data and the graph), the value i want occurred several times (before the peak or max and after that) and the formula i use will pick the later. Is there anyway to limit the search up to the max value only, so that it will pick the value before the peak? The formula i want to write is general so that i can use it with any data i have. Your effort is much appreciated. Regards, Udea |
#8
|
||||
|
||||
Hi udea,
Matching works best with a exact number. In D2 you can use the array formula (Ctrl-Shift-Enter): =MAX(IF(A3:OFFSET(A3,MATCH(MAX(A3:A122),A3:A122,0)-1,0)<=MAX(A3:A122)/2,A3:OFFSET(A3,MATCH(MAX(A3:A122),A3:A122,0)-1,0))) In E2 you can use the ordinary formula: =INDEX(B3:B122,MATCH(D2,A3:A122,0))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Mr. Macropod,
Awesome. I have no idea that we can write a formula that way. A very long way for me learn. You really are my savior and thank you very much. Regards, Udea |
#10
|
||||
|
||||
Hi udea,
Your mission now is to figure out how the formula works ...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Mr. Macropod,
I'm working on it right now but 1 thing i noticed is that you used the Offset to set the range, making it what i would call 'dynamic range' or 'range variable'. Am I right? Anyhow, i'll keep study the formula and test on other data sets and later (by this evening) i'll get back to you. Thank you, Sensei... Regards, Udea |
#12
|
|||
|
|||
But at a glance, it look almost similar to that 1st formula you posted and the difference is just that u dont make the end of range is fixed but make it find the max value and end the range there rite? (i dont know yet, still configuring it)...
|
#13
|
||||
|
||||
Hi udea,
Yes, the formula uses the offset function to derive a dynamic range, the end of which is the maximum value.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multi-Variable Lookup help | ebolton | Excel | 8 | 05-05-2011 05:28 AM |
Field Lookup - Confused | lawnG | Word Tables | 1 | 01-31-2011 03:50 PM |
Creating Lookup in Excel | vikash kumar | Excel | 2 | 12-06-2010 06:50 AM |
Using the LOOKUP Command | Grapejuice | Excel | 2 | 10-15-2008 02:02 PM |
Help with Max or lookup fomula to return a name forAmaxValue | dutch4fire23 | Excel | 0 | 07-28-2006 01:12 PM |