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
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 FunctionThis 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
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.
