I have a 2D data range (all numbers) on one worksheet, and from another worksheet I'd like to look up the closest value to an input. From there I need to know the column and row header names, and that's it. It sounds pretty simple, but I can't figure out how to do it without long convoluted loops and if statements. Can anyone point me toward an elegant solution?
Thanks in advance!
By nearest we presume this means above/below ?
If we assume data is stored on Sheet1 in A1:D100 and data point of interest is entered into A1 on Sheet2 then the below gets co-ordinates of closes match in B2:D100 range (given A and row 1 are headers)
Please Login or Register to view this content.
Note text in red - ie Array.
From which you can retrieve associated values:
Please Login or Register to view this content.
Adjust ranges to suit obviously
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks