Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Index Match
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Index Match
Resolved · Medium Priority · Version 2007
Reply Mon 28 Feb 2011, 11:17
Edited on Mon 28 Feb 2011, 14:21Delegate James said...
James has attended:
Excel VBA Intro Intermediate course
Index Match
Hi there,
On my spreadsheet, I have this formula working (ctrl + sft + enter), but I am trying to include this in a loop within a module.
=INDEX('Price Chart'!$B$292:$B$579,MATCH(MIN(ABS('Price Chart'!$B$292:$B$579-D15)),ABS('Price Chart'!$B$292:$B$579-D15),0),1)
However, upon trying to type the code of it, it keeps coming up with a #VALUE error... could you tell me if there is something obviously wrong with it?
Many Thanks
Function IndexingPrice(thePrice)
IndexingPrice = WorksheetFunction.Index(Sheets("Price Chart").Range("b292:b579"), _
WorksheetFunction.Match(WorksheetFunction.Min(Abs(Sheets("Price Chart").Range("b292:b579") - _
thePrice)), Abs(Sheets("Price Chart").Range("b292:b579") - thePrice), 0), 1)
End Function
For upcoming training course dates see: Pricing & availability
RE: Index Match
Hi James
Thank you for your question
You are working with array functions here, which clearly return multiple values. In the above example you are passing the results directly into the function, which can only save a single value.
I suggest the following solution
Function IndexingPrice(thePrice)
Dim Results as variant
Results = WorksheetFunction.Index(Sheets("Price Chart").Range("b292:b579"), _
WorksheetFunction.Match(WorksheetFunction.Min(Abs(Sheets("Price Chart").Range("b292:b579") - _
thePrice)), Abs(Sheets("Price Chart").Range("b292:b579") - thePrice), 0), 1)
indexPrice = results
End Function
This is difficult to test without having the worksheet in question, and also without a deep understanding of exactly what you are doing, but in principle the above first writes the data into a variant data type (which can store multiple values) and then sets the function equal to this.
I suggest attempting the above solution and getting back to me with any error messages that arise
Regards
Stephen
Reply Sat 5 Mar 2011, 17:56Delegate James said...
RE: Index Match
sorry Stephen, it still doesnt seem to be working... I will try to explain a little better what I am trying to do
A B
2.023216512 0.02
1.987511254 0.03
1.962321421 0.04
1.921541452 0.05
1.901564321 0.06
basically, I am trying to create a function that will find the closest match to a given number(thePrice, eg, 1.92) and display that, as I then need to use that number in a vlookup that will eventually return me the corresponding value from column B.
So, if thePrice is 1.92, i want it to look down column A, find the closest possible match, and then return its corresponding value from column B.
Sorry that it sounds so complicated, I will continue trying to figure it out too.
Regards
James
RE: Index Match
Hi James
I apologise for the delay in getting back to you. This was due to an oversight on my part
I appreciate that you may now have resolved the problem. If not let me know and I will give it my urgent attention
Regards
Stephen
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Thu 7 Jul 2011: Automatically marked as resolved.
|