Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-23-2011, 10:00 PM
udea udea is offline Lookup a value from non-sorted data Windows 7 64bit Lookup a value from non-sorted data Office 2007
Novice
Lookup a value from non-sorted data
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 04-24-2011, 02:11 AM
macropod's Avatar
macropod macropod is offline Lookup a value from non-sorted data Windows 7 32bit Lookup a value from non-sorted data Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
Reply With Quote
  #3  
Old 04-24-2011, 03:26 AM
udea udea is offline Lookup a value from non-sorted data Windows 7 64bit Lookup a value from non-sorted data Office 2007
Novice
Lookup a value from non-sorted data
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default

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
Attached Files
File Type: xlsx Book1.xlsx (12.2 KB, 10 views)
Reply With Quote
  #4  
Old 04-24-2011, 06:00 AM
macropod's Avatar
macropod macropod is offline Lookup a value from non-sorted data Windows 7 32bit Lookup a value from non-sorted data Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 04-24-2011, 06:43 AM
udea udea is offline Lookup a value from non-sorted data Windows 7 64bit Lookup a value from non-sorted data Office 2007
Novice
Lookup a value from non-sorted data
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default

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
Attached Files
File Type: xlsx Book1.xlsx (17.0 KB, 8 views)
Reply With Quote
  #6  
Old 04-24-2011, 03:06 PM
macropod's Avatar
macropod macropod is offline Lookup a value from non-sorted data Windows 7 32bit Lookup a value from non-sorted data Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by udea View Post
Value 151 is actually =max(a3:a122)/2, which is at row 23.
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:
Originally Posted by udea View Post
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).
So, you want the nearest match before the average is first reached? What happens if the first value is the maximum?

You need to set out fully what the matching logic is.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-24-2011, 10:32 PM
udea udea is offline Lookup a value from non-sorted data Windows 7 64bit Lookup a value from non-sorted data Office 2007
Novice
Lookup a value from non-sorted data
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 04-24-2011, 10:59 PM
macropod's Avatar
macropod macropod is offline Lookup a value from non-sorted data Windows 7 32bit Lookup a value from non-sorted data Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #9  
Old 04-25-2011, 01:31 AM
udea udea is offline Lookup a value from non-sorted data Windows 7 64bit Lookup a value from non-sorted data Office 2007
Novice
Lookup a value from non-sorted data
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 04-25-2011, 01:55 AM
macropod's Avatar
macropod macropod is offline Lookup a value from non-sorted data Windows 7 32bit Lookup a value from non-sorted data Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi udea,

Your mission now is to figure out how the formula works ...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 04-25-2011, 02:24 AM
udea udea is offline Lookup a value from non-sorted data Windows 7 64bit Lookup a value from non-sorted data Office 2007
Novice
Lookup a value from non-sorted data
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 04-25-2011, 02:29 AM
udea udea is offline Lookup a value from non-sorted data Windows 7 64bit Lookup a value from non-sorted data Office 2007
Novice
Lookup a value from non-sorted data
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default

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)...
Reply With Quote
  #13  
Old 04-25-2011, 04:34 AM
macropod's Avatar
macropod macropod is offline Lookup a value from non-sorted data Windows 7 32bit Lookup a value from non-sorted data Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi-Variable Lookup help ebolton Excel 8 05-05-2011 05:28 AM
Lookup a value from non-sorted data 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
Lookup a value from non-sorted data 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:28 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft