09-18-2020 04:39 AM
09-18-2020 04:39 AM
I have two Excel workbooks, WB A and WB B. WB A contains the source data in the form of a table. WB B contains a table that looks up data from WB A table using INDEX and MATCH functions.
Table in WB B does not show updated values unless WB A is open. I want table in WB B to update even when WB A is not open. What can I do to achieve that?
09-18-2020 07:41 AM
just for Info
The INDEX and MATCH matrix functions enable a data table (matrix) to be read out. Unlike VLOOKUP, areas to the left of the search criterion column can also be output. The INDEX function can also be used to address multiple matrix references.
The combination of the index and match functions in Excel is a kind of brother of the VLOOKUP.
Most Excel users are familiar with the VLOOPUP, whereas very few are familiar with the INDEX function. In combination with MATCH, this offers almost unlimited possibilities to track down data within a search area.
The index function
If you know the VLookup, you'll know that you can only use it to search tables from left to right. The index comparison function is more flexible: it also browses from top to bottom.
The INDEX function
With the help of the INDEX function you can find values within a defined cell range depending on your position.
With the INDEX function, you can locate values precisely to the cells.
The MATCH function
Now let's look at the MATCH function. It searches a range of cells according to certain criteria and, if found, returns its relative position within the range.
The INDEX MATCH function
Now it's getting exciting: if we combine INDEX with COMPARE, we get a function that is so powerful that not even the VLOOKUP can hold a candle to it. You can use this to determine the values of individual cells within a defined range using search criteria.
With the help of the dynamic duo INDEX MATCH, we can search through the entire matrix and save the tedious task of counting through the individual columns.
Accordingly, with Index (comparison) in Excel, large data areas can be easily searched for any criteria. MATCH can search for both a row and a column number.
I would be happy to know if I could help.
I know I don't know anything (Socrates)