Index and Match on multiple tabs

New Contributor

Hello guys,
I need your expertise to get some help on the following:

I have an xls sheet containing Account types information that has three tabs:

- Page1

- Page2

- Summary

I do paste information on Page1 to obtain  one value located in column B (it could have 1 account type or multiple account types)

I do paste information on Page2 to obtain three values located in colum B, C and D (it could have 1 account type or multiple account types)

Both information comes from different sources but fortunatelly they have the same format.

Also, if I have 4 account types to paste on page1, I also have the same 4 lines (accounts) on page 2 but they have different information.

 

The summary tab has a list of Account type names and the other columns has empty fields to insert Unit number, Book Value, Market Value and Average price.

The Unit value must be obtained from the information pasted on Page1

The Book value, Market value and AVG price are contained on Page2

 

I'm currently using a VLookup formula to get the specific values from Page1 and Page2 on Summary tab, however, since we have a list of more that 600 mutual fund names,  the vlookup formula is getting the file too big and slow to process the information.

 

Do you think it will be a way to use Index & Match formula on this case? 

I hope it is well explained and I can get some feedback from your end.

Thank you in advance for any help you can provide.

2 Replies
I do not follow what you are doing but VLookup(target;table;col) is functionally equivalent to index(table;match(target;index(table;0;1);false);col). It is unlikely going to give you better performance unless you look up multiple values within the same result row, when a single MATCH will do, the row number of which can then be reused in multiple calls on INDEX.
Hi @ecovonrein and thanks for your reply.
What I basically need to do is to reduce the amount of resources my file is using by having around 1,800 cells that have vlookup formula on it.

Long story short: I need to get values from Page1 and Page2 that matches with a column name on page "summary".
Page1 and Page2 contains data pasted from other site, so those pages have different values every time we need to produce the matching info in "summary" tab.
Not sure if this is clear.
Please let me know if you have any other idea on how to figured out and thanks in advance.