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

(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:

Raw
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

=INDEX([Book2]Sheet2!$A$2:$D$6,MATCH(A2,[Book2]Sheet2!$A$2:$A$6,0),MATCH(B1,[Book2]Sheet2!$A$1:$D$1,0))

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 *