How to offset the match before applying the index

[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.

 

[Hany ali]

how to help you without the file ?!!!!!
Pls Read the Rules

 

[Jayce]

I am extremely sorry for that...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. Sheet a is non-editable, which contains a value in only one cell of column A, i.e., A2

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.

 

[GraH - Guido]

Since match returns an index, simply do a -1 I'd say...

 

[Jayce]

I tried it, it's returning zero. You can check it on the sheet I have attached

 

[Hany ali]

try

 

[Fluff13]

How about
=LOOKUP(2,1/(a!$A$2:INDEX(a!A:A,MATCH(D2,a!$D$2:$D$13,0))<>""),a!$A$2:INDEX(a!A:A,MATCH(D2,a!$D$2:$D$13,0)))

 

[Jayce]

We can't change anything in Sheet a. We need to derive the results based upon the values in A2 of Sheet a

 

[Fluff13]

Slight mod to my formula
=LOOKUP(2,1/(a!$A$2:INDEX(a!A:A,MATCH(D2,a!$D$2:$D$13,0)+1)<>""),a!$A$2:INDEX(a!A:A,MATCH(D2,a!$D$2:$D$13,0)+1))

 

[Jayce]

Hi Fluff13,

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]

Why have you asked this question twice https://chandoo.org/forum/threads/how-to-use-index-for-match-that-is-offset-by-one-row.42643/

 

[Jayce]

Because these both are different forums I believe: One is for Excel and other for VBA Code......
I am new to this platform, please let me know if I have done something wrong

 

[Fluff13]

You are quite right, the other thread is in the VBA forum & this one is in the Excel section, which is why everyone has been providing formula solution

 

[Fluff13]

Did the formula I provided work?

 

[Jayce]

@Fluff13,

It's working well on the new workbook I attached. Thanks!!!!

 

[Fluff13]

Ok, to get the code, simply turn on the macro recorder, select the first cell with the formula & press F2 then Enter.
Turn of the recorder & you have the basis for the code.

 

[Fluff13]

Because the Match starts in row 2, it could alternatively be written like
=LOOKUP(2,1/(a!$A$2:INDEX(a!A:A,MATCH(D2,a!$D$1:$D$25,0))<>""),a!$A$2:INDEX(a!A:A,MATCH(D2,a!$D$1:$D$25,0)))
So the match starts at row1 & so you don't need the +1

 

[Jayce]

I have to look for its vba coding. VBA coding is only available for Vlookup but not Lookup

 

[bosco_yip]

Here's a shorter formula version and will return the result as same as Fluff's formula :

=LOOKUP("zzz",a!$A$2:INDEX(a!$A$2:$A$13,MATCH(D2,a!$D$2:$D$13,0)))

Regards
Bosco

 

[Fluff13]

Borrowing Bosco's formula try

Sub Jayce()
   With Sheets("b")
      With .Range("A2", .Range("D" & Rows.Count).End(xlUp).Offset(, -3))
         .Formula = "=LOOKUP(""zzz"",a!$A$2:INDEX(a!$A:$A,MATCH(D2,a!$D:$D,0)))"
         .Value = .Value
      End With
   End With
End Sub

 

[Jayce]

@bosco_yip and @Fluff13

Excellent Job! It works like a miracle.

 

[Fluff13]

Glad we could help & thanks for the feedback