Macro To Match Data Amd List Info From One Workbook To Another

[JimmiOO]

Could someone please help me with this, I am trying to extract data from one sheet to another.

On my master book I need to fill information in I, J, K & L columns, this information can be found in the info book.

What I need to do is match up the data in the master book C column against the data in the info book C column, if it finds a match I will like to fill the master book columns I, J, K & L with data from the info book like this

Master book I column will be found in info book F column
Master book J column will be found in info book E column
Master book K column will be found in info book A column
Master book L column will be found in info book B column
Master book M column will be found in info book H column

I have attached the sample files, any help will be greatly appreciated.

[JBeaucaire]

1) open both sheets
2) In Master put these formulas:

I2: =IF(ISNUMBER(MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0)), INDEX([info.xls]CRTG_FOOD_Price_File!F:F, MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0)), "")

J2: =IF(ISNUMBER(MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0)), INDEX([info.xls]CRTG_FOOD_Price_File!E:E, MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0)), "")

K2: =IF(ISNUMBER(MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0)), INDEX([info.xls]CRTG_FOOD_Price_File!A:A, MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0)), "")

L2: =IF(ISNUMBER(MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0)), INDEX([info.xls]CRTG_FOOD_Price_File!B:B, MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0))&"", "")

M2: =IF(ISNUMBER(MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0)), INDEX([info.xls]CRTG_FOOD_Price_File!H:H, MATCH($C2, [info.xls]CRTG_FOOD_Price_File!$C:$C, 0))&"", "")

Now copy those cells down...


You can now close the Info sheet if you wish.

You can flatten all these values permanently by selecting columns I:M and copying, then edit > paste special > values.

[JimmiOO]

Hi, thanks very much for that it does work well with the formula but i was after a macro if possible? the reason being the info file and the master file will have many thousand columns, for quickness i was going to add a button to the master so it could all be done in one click.

Would it be possible to incorporate the forumla into a macro?

[JBeaucaire]

Sure, record a macro of you putting your formulas into the cells, copying down as far as needed, then doing a copy/paste special/values to flatten the values.