Macro for INDEX and MATCH

Where do the new entries get made - on both sheet1 and sheet2 or just on sheet2? The problem is that I don't understand what you require

I can not attach the original table, therefore, put an example.

The first and second tables may be different (it can be anything). But they always have a shared column (it has a partial or full match) by which to make comparisons.

All that is required is to replace the formula of a function or macro. To be able to search for data from the first table in the second range and pull the desired value.

Therefore, I ask to help with the universal macro for any table with proposal selection ranges and output. For a range or a single column (see formula in example).

At present, a few problems:
1. Formula for a long time working on large data (250k+ rows)
2. The table of the first sheet may be set filter. And needs to compare visible values on the filter with the second sheet (which may also be a filter). It is necessary to compare both the filtered and unfiltered ranges only visible values);
3. 3. In large data uncomfortable stretching formula in the filtered range.

Hi Remphan,

I guess I'm a little slow - but, it seems that you haven't answered my question

So, with a change on the second we must update the first sheet? I'm thinking of event code

Originally Posted by xladept

So, with a change on the second we must update the first sheet? I'm thinking of event code

Sometimes* is a one-time action. Then I copy/paste the values (obtained by the formula) without format.
Copy formula result and paste the values in the case of filtered range - uncomfortable. So needs remove the filter from all over the sheet, and copy and paste the values without format the entire column or a range.

The second table may be located in another book. Just for the convenience of using a formula that does not get lost in the many my workbooks I copy a second table from another workbook in the second sheet current book. After receiving the data by the formula and paste data as a value - delete the second sheet.

It is not difficult to do. The issue was initially that the formula works for a long time if the book contains a lot of rows and columns (macro or vba function will be faster). And that uncomfortable stretching formula on filter case. That is, if the range has a filter, then compare only the visible region and inserted into the visible range results.

*But sometimes it is required and automatic recalculation. That is sometimes the second table on the second sheet and leave to automatically recalculate.

Hi Remphan,

I've submitted this thread to the community of experts as I just don't know what you need. You may be getting questions from some other experts now

Originally Posted by xladept

Hi Remphan,

I've submitted this thread to the community of experts as I just don't know what you need. You may be getting questions from some other experts now

Ok. Just what I needs is change formula H3:H69 and J3:M69 by function or macro. If the vba function - that I will can stretch it into cells where it is needed. In case of macro - each time it will be difficult to frequently change the code (a lot of different tables, and books for comparison), so I would like him to ask - which ranges compare and where you want to insert data.

In short, just ask a vba function to replace the current formulas in example at my 1st post (for column only like H3:H69 or range like J3:M69) if someone has the opportunity to do it. Because vba will run faster than formula at 250-500k rows

If anybody can help - help, pls.
Cross for issue