Index Match

Resolved · Medium Priority · Version 2007

Mon 28 Feb 2011, 11:17 Edited on Mon 28 Feb 2011, 14:21 Delegate 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

Fri 4 Mar 2011, 12:00 Trainer Stephen said...

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

Sat 5 Mar 2011, 17:56 Delegate 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

Tue 31 May 2011, 15:21 Trainer Stephen said...

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

Fri 1 Jul 2011, 09:22 Trainer Richard said...

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.

Training courses