Index and Match Not working

**HeHeHaHa** · 12-15-2014, 11:28 PM

Hi I have a problem returning a value using index and match. May I know what is the problem? Thanks

Private Sub lookup()
Sheets("Data").Select
Cells(4, 3) = Application.Index(Worksheets("Hello World").Range("A1:Z100"), Application.Match(Worksheets("Data").Cells(4, 1), Worksheets("Hello World").Range("A1:Z100")), Application.Match(Worksheets("Data").Cells(2, 2), Worksheets("Hello World").Range("A1:Z100")))
End Sub

In my Data sheet Cells(4,3) i am suppose to look for a value of row( 20141101) and column (waveheight) in the "Hello World" Worksheets. The respective position of the value i am searching for is at the H2 (value 1.2) but the result return is #N/A. Thanks

**mehmetcik** · 12-16-2014, 12:17 AM

1. You are over complicating this.

I would not use index within VBA, what benifits does it give you?

application.index(A1:A1000,25)

is the same as

range("A1").offset(25-1,0).value

or

range("A" & 25).value)

Both my solutions will be a lot faster than using application.index

2. The Match function is usually used with a zero.

MATCH(A6,$B$2:$B$4,0)

3. However Application.match is very slow consider using find,

    With Range("B1:B1000")
Set rngfind = .Find("12", .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
    LR = rngfind.offset(0,1).value
    End With

Index and Match Not working

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Index and Match Not working

Re: Index and Match Not working

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Working INDEX MATCH with SEARCH, but I need to add another MATCH to the formula!

Correct/Working (Index,Match) formula not working between cells

Index Match NOT Working

INDEX MATCH MATCH working great and then failing on me.

INDEX MATCH MATCH working fine and then NOT WORKING for a random row or two.

Bookmarks

Bookmarks

Posting Permissions