Insert Formula in Macro

[MJA001]

I'd like to insert the formula that does a great Index Match job for me. But I'd like to automate it now and can't figure out how.

INDEX(Project2!B:B, MATCH(Project!A:A,Project2!G:G,0))

So in my Workbook, I'd like this Matches from the formula to be displayed in column BJ of Worksheet "Project" .


Thanks in advance for any help.

 

[cerfani]

match wont work with multiple lookup values

see if this article will help, i didnt read it thoroughly but it appears to match your situation... https://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list

 

[MJA001]

There is only one look up value and I couldn't discern from the article to apply it to use in a Macro.

In simpler terms, the formula matches the data from worksheet "project", column A to the same data from Worksheet "Project2" column G. Then it indexes the match to populate the data from worksheet "Project2", Column B into the corresponding cells in worksheet "Project" column BJ.

So it should work, but I'm lost as to converting the formula into a macro.

 

[cerfani]

MATCH(Project!A:A,Project2!G:G,0) is wrong if you are only trying to lookup a single value ... Project!A:A probably just grabs whatever value is in A1.

This would be valid:

MATCH(Project!A2,Project2!G:G,0)

that would return the first row number that A2 matches in column G

 

[MJA001]

Cerfani,

Thanks for the info, but I don't I'm doing a good job of communicating. I do not want to match a single value. I need the macro to look through all the cells, find the matches and display the corresponding value. The formula works great for this.

When I record a macro of myself adding it to a single cell, the formula =INDEX(Project2!B:B, MATCH(Project!A:A,Project2!G:G,0))

is changed into this: "=INDEX(Project2!C[-60], MATCH(Project!C[-61],Project2!C[-55],0))"


I can't figure out how to add it into a macro and apply to the entire column. Any suggestions?