Thread: [Solved] Lookup a value from non-sorted data
View Single Post
 
Old 04-24-2011, 06:00 AM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2000
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,375
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