Using index,match in vba between two sheets?

Question

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.

You are mixing up syntax for evaluating functions in VBA and implanting worksheet functions in the sheet using VBA. So you need application.index etc and ensure your range syntax is consistent - see how yours changes - the match bit is wrong. — SJR
– SJR, Commented Aug 14, 2019 at 16:33

jessi · Accepted Answer · 2019-08-14 16:38:12Z

2

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

answered Aug 14, 2019 at 16:38

jessi

1,5382 gold badges23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using index,match in vba between two sheets?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related