Forum Discussion

ftkdahma's avatar
ftkdahma
Copper Contributor
Sep 18, 2020

Excel Table does not update data

Hi,
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?
Thank you

excel

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 18, 2020

    ftkdahma 

     

    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.

     

    Nikolino

    I know I don't know anything (Socrates)

Resources