How to use Index for Match that is offset by one row

[Jayce]

Hi,
I am struggling to find a way to offset the match by one row above (-1,0) and then apply the index.
In my workbook, I have two sheets- named as a and b. Following is the formulae I am using without offset:
b.Cells(k, 1) = Application.WorksheetFunction.Index(a.Range("A:A"), Application.WorksheetFunction.Match(b.Cells(k, 4), a.Range("D:D"), 0))

I want to offset the position found by match to one row above in sheet a, followed by index.

 

[salim hasan]

TRY

b.Cells(k, 1) = _
  Application.Index(a.Range("A:A"), Application.Match(b.Cells(k, 4), a.Range("D:D"), 0)-1)

 

[Jayce]

Hi,
The above formula is returning zero. Let's make it simple, I am attaching the workbook with two sheets, named as a and b.
Results are needed to be derived in Column A of Sheet b, based upon data in Sheet a.

INDEX(a!$A$2:$A$13,MATCH(D3, a!$D$2:$D$13,0))

I am using the above index and match to get the results by matching the Column D, i.e. Node Id. However, I am not able to get results in A5 and A6 of Sheet b because the corresponding cells in column A of sheet A are empty based on the matched position.

I want to offset the matched position by -1 (one row above), so that match can result: (Node Id = 20357 ) and Index can give me (Intersection: Highway 7 and Centre Street) in A4 and A5 of Column A of Sheet b.

 

[Jayce]

Please Help!

To make more clear what I am looking for:

I am attaching workbook again, I am looking for VBA code to get the name of intersections in Column A of Sheet b, i.e. Highway 7 and Centre St for A2:A7, and Highway 7 and Rivermede from A8: A12.

 

[Fluff13]

Duplicate of

How to offset the match before applying the index

Hi, I am struggling to find a way to offset the match by one row above (-1,0) and then apply the index. In my workbook, I have two sheets- named as a and b. Following is the formulae I am using without offset: b.Cells(k, 1) = Application.WorksheetFunction.Index(a.Range("A:A")...

chandoo.org