INDEX MATCH with lookup array range to CONCATENATE

[dluhut]

Hello,

Given that on a workbook 1, I have the following range

Concatenate	Course	Name
Math;David	Math	David
Math;John	Math	John
Math;Jane	Math	Jane
Science;David	Science	David
Science;John	Science	John
Science;Jane	Science	Jane

On the next workbook, which is a read only, has the following table

Course	Name	Grade
Math	David	A+
Math	John	A
Math	Jane	A-
Science	David	B-
Science	John	B
Science	Jane	B+

I know that I can use INDEX MATCH with array formula, but given that the number of rows are approximate thousands, and that the number of workbooks that I have to 'index match' per month are a lot (for the whole full year, it's until column CFD), so INDEX MATCH with array formula will not work, because it's taking too long to calculate.

So may I know what other options do I have.

*Note that workbook is on a network drive, thus it's external link

[AliGW]

I don't understand what it is you are trying to do - sorry. What are you wanting to do with the two tables you have shown us?

[dluhut]

So what I am trying to do is that, I know that I can use a an array formula for the concatenation, but it'll take a very long time in calculating.

So am wondering if there's a better way to get the same result but with faster calculation.

Please Login or Register  to view this content.

[FDibbins]

You still have not told us what - exactly - you are trying to do. You have told us what formulas you have used, but that still does not say what you want.

Show us a small sample of what you are working with, and what your expected outcome would be

[dluhut]

What I really want to do is accomplish the same result as the array formula that I've done.

But because the 'lookup' range is on a different workbook which is on the shared drive that I can only have read-only, I can't be able to create a helper column (thus the index match array formula).

However, due to many workbooks and the number of rows, the index match array formula is taking a long time to calculate.

Thus, am wondering if there's a way to achieve the same result as index match, but with a faster/instant calculation, so that upper level management, doesn't have to wait a long time for Excel to calculate every time they change from one ('COURSE') to another ('NAME')

[oeldere]

Maybe it can be done with pivot table.

But again, show us the excel file, without confidential information.

Please also add the expected result manualy in the file.