Using Match/Index on 2d Array

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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.