(Solved) – Is there an index like function in VBA which can return multiple column data on perfect match?


I have 2 workbooks with data like this:

Master Key File

As seen, column A is my unique key to map the 2 workbooks. The values i want to populate are the “TBD” in the Raw file (image1). So i’m looking for key 11x in Master File and get values for column B, C & D (assembly, sub and part) for that key. My attempt was to use


I also “tried” match func but couldn’t figure the destination to copy the search value and my code only returns one value at a time anyways…

for R = 2 To lastrow
    y=application.match(worksheet2.cells(R,1), worksheet2.range("A:A"),0)
If not application.isnumber(y) Then
    worksheet2.cells(x,1).copy destination:=worksheet1.cells(**?????????**)

So, can i return all three column values using one index match formula? If not how can i write the search function in VB? Please help.

Leave a Reply

Your email address will not be published. Required fields are marked *