CODEWITHSUNDEEP
excelvba
jrwaller
jrwaller

Reputation: 51

Using index,match in vba between two sheets?

I am taking a name from one sheet, indexing/matching to get data from a chart in another sheet, and then performing a calculation with that data. I am getting syntax errors with the line trying to perform the matching.

I have also tried vbLookup with similar syntax errors.

I have based this section of code off of this simple video: https://www.youtube.com/watch?v=BIGH-Q8cnrI I have also seen some stack overflow questions where users are trying to call data from different workbooks.

Say I am trying to Match "Maryland" from a cell (B4) in sheet2 to the following chart(in Range H96:J143 on sheet1), using this index to call the land cost value next to it.

State      Region     Land Cost
Delaware    NE         8300
Maryland    NE         6620
New Jersey  NE         12900

where my code to call this out right now is:

'statecost = Application.VLookup(Range("B4").Text, Sheet1.Range("H96:J143"), 2, False)
statecost =Index(Sheet1.Range("J96:J143"),match(B4,H96:H143,0))

I would want these lines to spit out a statecost = 6620. The index/match section gives me syntax errors while the vblookup line gives me no match.

Thank you.

Upvotes: 0

Views: 4043

Answers (1)

jessi
jessi

Reputation: 1518

You want to have the other sheet also included in the lookup of match.

Assuming that you have variables set to Sheet1 and Sheet2 that are worksheets, you could do this.

=WorksheetFunction.Index(Sheet1.Range("J96:J143"),_
 WorksheetFunction.Match(Sheet2.Range("B4"), Sheet1.Range("H96:H143"),0))

The VBA documentation is helpful here for both Index and Match

Upvotes: 2

Related Questions