Using Match/Index on 2d Array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ExcelMonkey - 21 Feb 2006 10:09 GMT

I have an array of data (HourData) with 20 rows and 6 columns as seen below.
I want to find the row where the value in the 6th column is equal to or
greater than a value (X = 2700). This should return the row 12:

8 D E 491 21 2918

I was simply going to use a Match/Index to do this on the 6 column of the
array:

Answer = Application.WorksheetFunction.Match(X, Application.Index(HourData,
0, 6), -1)

As I know that it may not an exact match, I have used the -1 at the end of
the match to find the smallest value that is greater than or equal to X.

However I am getting an error message saying "unable to get Match property"

What have I missed here?

Thanks
EM

***************************************************
7 W D 437 0 437
5 N E 73 0 510
7 W D 67 0 577
2 D D 87 5 664
8 D E 99 6 763
5 N E 492 10 1255
10 D C 309 11 1564
3 C B 87 13 1651
2 D D 313 13 1964
6 U C 211 14 2175
9 D E 252 18 2427
8 D E 491 21 2918
4 U E 82 22 3000
1 C E 241 22 3241
4 U E 57 23 3298
10 D C 69 30 3367
3 C B 207 31 3574
1 C E 61 33 3635
6 U C 81 34 3716
9 D E 84 49 3800

Reply to this Message

ExcelMonkey - 21 Feb 2006 12:00 GMT

Actually, is this failing because the data is sorted in column 6 in ascending
order? I know in Excel you cannot use the -1 within the Match function
unless the data is sorted in descending order?

EM

> I have an array of data (HourData) with 20 rows and 6 columns as seen below.
> I want to find the row where the value in the 6th column is equal to or
[quoted text clipped - 39 lines]
> 6 U C 81 34 3716
> 9 D E 84 49 3800

Reply to this Message

ExcelMonkey - 21 Feb 2006 12:47 GMT

I got around this by creating a 7th column and by building an If that creates
a TRUE/FALSE outcome by testing for X. Then I used the Match/Index looking
for an exact match on column 7

Answer = Application.Match(True, Application.Index(HourData, 0, 7), 0)

?Answer
12
***************************************************
7 W D 437 0 437 False
5 N E 73 0 510 False
7 W D 67 0 577 False
2 D D 87 5 664 False
8 D E 99 6 763 False
5 N E 492 10 1255 False
10 D C 309 11 1564 False
3 C B 87 13 1651 False
2 D D 313 13 1964 False
6 U C 211 14 2175 False
9 D E 252 18 2427 False
8 D E 491 21 2918 True
4 U E 82 22 3000 False
1 C E 241 22 3241 False
4 U E 57 23 3298 False
10 D C 69 30 3367 False
3 C B 207 31 3574 False
1 C E 61 33 3635 False
6 U C 81 34 3716 False
9 D E 84 49 3800 False

Reply to this Message

Tom Ogilvy - 21 Feb 2006 13:26 GMT

This worked for me without the added column.

Sub abc()
HourData = Range("A1").CurrentRegion.Resize(, 6)
X = 2700
Answer = Application.WorksheetFunction.Match(X, Application.Index(HourData,
0, 6), 1) + 1
MsgBox Answer
End Sub

You would actually have to check for an exact match first before adding the
1.

Signature

Regards,
Tom Ogilvy

> I got around this by creating a 7th column and by building an If that creates
> a TRUE/FALSE outcome by testing for X. Then I used the Match/Index looking
[quoted text clipped - 69 lines]
> > 6 U C 81 34 3716
> > 9 D E 84 49 3800

Reply to this Message