{"text_format":"3","workbook":"xlsx","n_sheets":3,"sheets":["Help","Arrays","Dynamic Tables"],"not_rendered":{"macros":1,"defined_names":3},"read":"whole","date_system":"1900"}

{"sheet":"Help","index":0,"n_rows":9,"n_cols":2,"rows_kept":9}
[null,"Help and Information"]
[]
[1,"Make a copy of this workbook if you plan to make any changes so you can revert back if things go wrong."]
[]
[2,"Leave sheets protected and select different Names to see how the dynamic ranges are affected."]
[]
[3,"This workbook is intended for training purposes and may save someone the frustration I have gone through understanding how these formulas function."]
[]
[4,"After using both Lookups and Index/Match it seems that Index/Match functions a bit better."]

{"sheet":"Arrays","index":1,"n_rows":21,"n_cols":11,"rows_kept":21,"merged":[[0,0,0,4],[0,6,0,10],[7,0,7,4],[7,6,7,10],[14,0,14,4],[14,6,14,10]]}
["Table 1 Reference",{"$nulls":5},"Dynamic Table 1"]
["Name","Street","City ","Zip Code","Phone Number",null,"Name","Street","City ","Zip Code","Phone Number"]
["Jack Henry","12 Main Street","Los Angeles",55555,5555555555,null,"Jack Henry",{"$f":"IFERROR(INDEX(B$3:B$7,MATCH($G3,$A$3:$A$7,0)),\"\")","v":"12 Main Street"},{"$f":"IFERROR(INDEX(C$3:C$7,MATCH($G3,$A$3:$A$7,0)),\"\")","v":"Los Angeles"},{"$f":"IFERROR(INDEX(D$3:D$7,MATCH($G3,$A$3:$A$7,0)),\"\")","v":55555},{"$f":"IFERROR(INDEX(E$3:E$7,MATCH($G3,$A$3:$A$7,0)),\"\")","v":5555555555}]
["Mark Anthony","1234 Park Avenue","Dallas",88889,1111111111,null,"Mark Anthony",{"$f":"IFERROR(INDEX(B$3:B$7,MATCH($G4,$A$3:$A$7,0)),\"\")","v":"1234 Park Avenue"},{"$f":"IFERROR(INDEX(C$3:C$7,MATCH($G4,$A$3:$A$7,0)),\"\")","v":"Dallas"},{"$f":"IFERROR(INDEX(D$3:D$7,MATCH($G4,$A$3:$A$7,0)),\"\")","v":88889},{"$f":"IFERROR(INDEX(E$3:E$7,MATCH($G4,$A$3:$A$7,0)),\"\")","v":1111111111}]
["Mary Martin","13 Main Street","Austin",47478,4444444444,null,"Mary Martin",{"$f":"IFERROR(INDEX(B$3:B$7,MATCH($G5,$A$3:$A$7,0)),\"\")","v":"13 Main Street"},{"$f":"IFERROR(INDEX(C$3:C$7,MATCH($G5,$A$3:$A$7,0)),\"\")","v":"Austin"},{"$f":"IFERROR(INDEX(D$3:D$7,MATCH($G5,$A$3:$A$7,0)),\"\")","v":47478},{"$f":"IFERROR(INDEX(E$3:E$7,MATCH($G5,$A$3:$A$7,0)),\"\")","v":4444444444}]
["Karen Fairchild","78 Westchester Ave","Port Chester",75073,5454545454,null,"Karen Fairchild",{"$f":"IFERROR(INDEX(B$3:B$7,MATCH($G6,$A$3:$A$7,0)),\"\")","v":"78 Westchester Ave"},{"$f":"IFERROR(INDEX(C$3:C$7,MATCH($G6,$A$3:$A$7,0)),\"\")","v":"Port Chester"},{"$f":"IFERROR(INDEX(D$3:D$7,MATCH($G6,$A$3:$A$7,0)),\"\")","v":75073},{"$f":"IFERROR(INDEX(E$3:E$7,MATCH($G6,$A$3:$A$7,0)),\"\")","v":5454545454}]
["Albert Finney",{"$nulls":6},{"$f":"IFERROR(INDEX(B$3:B$7,MATCH($G7,$A$3:$A$7,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(C$3:C$7,MATCH($G7,$A$3:$A$7,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(D$3:D$7,MATCH($G7,$A$3:$A$7,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(E$3:E$7,MATCH($G7,$A$3:$A$7,0)),\"\")","v":""}]
["Table 2 Reference",{"$nulls":5},"Dynamic Table 2"]
["Name","Street","City ","Zip Code","Phone Number",null,"Name","Street","City ","Zip Code","Phone Number"]
["Jack Henry","9 Laurel Lane","Austin",77777,7777777777,null,"Jack Henry",{"$f":"IFERROR(INDEX(B$10:B$14,MATCH($G10,$A$10:$A$14,0)),\"\")","v":"9 Laurel Lane"},{"$f":"IFERROR(INDEX(C$10:C$14,MATCH($G10,$A$10:$A$14,0)),\"\")","v":"Austin"},{"$f":"IFERROR(INDEX(D$10:D$14,MATCH($G10,$A$10:$A$14,0)),\"\")","v":77777},{"$f":"IFERROR(INDEX(E$10:E$14,MATCH($G10,$A$10:$A$14,0)),\"\")","v":7777777777}]
["Mary Martin","24 East Chase Parkway","Alberta",12128,8888888888,null,"Mary Martin",{"$f":"IFERROR(INDEX(B$10:B$14,MATCH($G11,$A$10:$A$14,0)),\"\")","v":"24 East Chase Parkway"},{"$f":"IFERROR(INDEX(C$10:C$14,MATCH($G11,$A$10:$A$14,0)),\"\")","v":"Alberta"},{"$f":"IFERROR(INDEX(D$10:D$14,MATCH($G11,$A$10:$A$14,0)),\"\")","v":12128},{"$f":"IFERROR(INDEX(E$10:E$14,MATCH($G11,$A$10:$A$14,0)),\"\")","v":8888888888}]
["Mark Anthony","54 Lake Avenue","Houston",56568,3333333333,null,"Mark Anthony",{"$f":"IFERROR(INDEX(B$10:B$14,MATCH($G12,$A$10:$A$14,0)),\"\")","v":"54 Lake Avenue"},{"$f":"IFERROR(INDEX(C$10:C$14,MATCH($G12,$A$10:$A$14,0)),\"\")","v":"Houston"},{"$f":"IFERROR(INDEX(D$10:D$14,MATCH($G12,$A$10:$A$14,0)),\"\")","v":56568},{"$f":"IFERROR(INDEX(E$10:E$14,MATCH($G12,$A$10:$A$14,0)),\"\")","v":3333333333}]
["Karen Fairchild","898 Long View Lane","Charlotte",12587,3232656599,null,"Karen Fairchild",{"$f":"IFERROR(INDEX(B$10:B$14,MATCH($G13,$A$10:$A$14,0)),\"\")","v":"898 Long View Lane"},{"$f":"IFERROR(INDEX(C$10:C$14,MATCH($G13,$A$10:$A$14,0)),\"\")","v":"Charlotte"},{"$f":"IFERROR(INDEX(D$10:D$14,MATCH($G13,$A$10:$A$14,0)),\"\")","v":12587},{"$f":"IFERROR(INDEX(E$10:E$14,MATCH($G13,$A$10:$A$14,0)),\"\")","v":3232656599}]
[{"$nulls":7},{"$f":"IFERROR(INDEX(B$10:B$14,MATCH($G14,$A$10:$A$14,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(C$10:C$14,MATCH($G14,$A$10:$A$14,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(D$10:D$14,MATCH($G14,$A$10:$A$14,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(E$10:E$14,MATCH($G14,$A$10:$A$14,0)),\"\")","v":""}]
["Table 3 Reference",{"$nulls":5},"Dynamic Table 3"]
["Name","Street","City ","Zip Code","Phone Number",null,"Name","Street","City ","Zip Code","Phone Number"]
["Jack Henry","5 Spring Avenue","Dallas",99999,9999999999,null,"Mary Martin",{"$f":"IFERROR(INDEX(B$17:B$21,MATCH($G17,$A$17:$A$21,0)),\"\")","v":"21 Palmer Street"},{"$f":"IFERROR(INDEX(C$17:C$21,MATCH($G17,$A$17:$A$21,0)),\"\")","v":"Des Moines"},{"$f":"IFERROR(INDEX(D$17:D$21,MATCH($G17,$A$17:$A$21,0)),\"\")","v":25971},{"$f":"IFERROR(INDEX(E$17:E$21,MATCH($G17,$A$17:$A$21,0)),\"\")","v":6666666666}]
["Mary Martin","21 Palmer Street","Des Moines",25971,6666666666,null,"Jack Henry",{"$f":"IFERROR(INDEX(B$17:B$21,MATCH($G18,$A$17:$A$21,0)),\"\")","v":"5 Spring Avenue"},{"$f":"IFERROR(INDEX(C$17:C$21,MATCH($G18,$A$17:$A$21,0)),\"\")","v":"Dallas"},{"$f":"IFERROR(INDEX(D$17:D$21,MATCH($G18,$A$17:$A$21,0)),\"\")","v":99999},{"$f":"IFERROR(INDEX(E$17:E$21,MATCH($G18,$A$17:$A$21,0)),\"\")","v":9999999999}]
["Mark Anthony","12 Mountain View","Denver",23985,2222222222,null,"Mark Anthony",{"$f":"IFERROR(INDEX(B$17:B$21,MATCH($G19,$A$17:$A$21,0)),\"\")","v":"12 Mountain View"},{"$f":"IFERROR(INDEX(C$17:C$21,MATCH($G19,$A$17:$A$21,0)),\"\")","v":"Denver"},{"$f":"IFERROR(INDEX(D$17:D$21,MATCH($G19,$A$17:$A$21,0)),\"\")","v":23985},{"$f":"IFERROR(INDEX(E$17:E$21,MATCH($G19,$A$17:$A$21,0)),\"\")","v":2222222222}]
["Susan Donovan","232 Yorkshire Street","London","235-23235","1-99965656666",null,"Susan Donovan",{"$f":"IFERROR(INDEX(B$17:B$21,MATCH($G20,$A$17:$A$21,0)),\"\")","v":"232 Yorkshire Street"},{"$f":"IFERROR(INDEX(C$17:C$21,MATCH($G20,$A$17:$A$21,0)),\"\")","v":"London"},{"$f":"IFERROR(INDEX(D$17:D$21,MATCH($G20,$A$17:$A$21,0)),\"\")","v":"235-23235"},{"$f":"IFERROR(INDEX(E$17:E$21,MATCH($G20,$A$17:$A$21,0)),\"\")","v":"1-99965656666"}]
["Karen Fairchild",{"$nulls":5},"Karen Fairchild",{"$f":"IFERROR(INDEX(B$17:B$21,MATCH($G21,$A$17:$A$21,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(C$17:C$21,MATCH($G21,$A$17:$A$21,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(D$17:D$21,MATCH($G21,$A$17:$A$21,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(E$17:E$21,MATCH($G21,$A$17:$A$21,0)),\"\")","v":0}]

{"sheet":"Dynamic Tables","index":2,"n_rows":21,"n_cols":10,"rows_kept":21,"merged":[[0,0,0,4],[0,6,0,10],[2,6,2,10],[4,6,4,7],[4,9,4,10],[6,6,8,7],[6,9,7,10],[7,0,7,4],[10,6,10,7],[10,9,10,10],[12,6,14,7],[12,9,14,10],[14,0,14,4],[16,6,17,10],[19,6,20,10]]}
["Dynamic Table 1",{"$nulls":5},"The 3 tables may have the same names but the information is different to show the references only apply to the table in question."]
["Name","Street","City ","Zip Code","Phone Number"]
["Jack Henry",{"$f":"IFERROR(INDEX(Arrays!B$3:B$7,MATCH($A3,Arrays!$A$3:$A$7,0)),\"\")","v":"12 Main Street"},{"$f":"IFERROR(INDEX(Arrays!C$3:C$7,MATCH($A3,Arrays!$A$3:$A$7,0)),\"\")","v":"Los Angeles"},{"$f":"IFERROR(INDEX(Arrays!D$3:D$7,MATCH($A3,Arrays!$A$3:$A$7,0)),\"\")","v":55555},{"$f":"IFERROR(INDEX(Arrays!E$3:E$7,MATCH($A3,Arrays!$A$3:$A$7,0)),\"\")","v":5555555555},null,"IFERROR(INDEX(Arrays!B$3:B$7,MATCH($A3,Arrays!$A$3:$A$7,0)),\"\")"]
["Mary Martin",{"$f":"IFERROR(INDEX(Arrays!B$3:B$7,MATCH($A4,Arrays!$A$3:$A$7,0)),\"\")","v":"13 Main Street"},{"$f":"IFERROR(INDEX(Arrays!C$3:C$7,MATCH($A4,Arrays!$A$3:$A$7,0)),\"\")","v":"Austin"},{"$f":"IFERROR(INDEX(Arrays!D$3:D$7,MATCH($A4,Arrays!$A$3:$A$7,0)),\"\")","v":47478},{"$f":"IFERROR(INDEX(Arrays!E$3:E$7,MATCH($A4,Arrays!$A$3:$A$7,0)),\"\")","v":4444444444}]
["Albert Finney",{"$f":"IFERROR(INDEX(Arrays!B$3:B$7,MATCH($A5,Arrays!$A$3:$A$7,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(Arrays!C$3:C$7,MATCH($A5,Arrays!$A$3:$A$7,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(Arrays!D$3:D$7,MATCH($A5,Arrays!$A$3:$A$7,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(Arrays!E$3:E$7,MATCH($A5,Arrays!$A$3:$A$7,0)),\"\")","v":0},null,"Arrays!B$3:B$7",null,null,"Arrays!$A$3:$A7$"]
["Mary Martin",{"$f":"IFERROR(INDEX(Arrays!B$3:B$7,MATCH($A6,Arrays!$A$3:$A$7,0)),\"\")","v":"13 Main Street"},{"$f":"IFERROR(INDEX(Arrays!C$3:C$7,MATCH($A6,Arrays!$A$3:$A$7,0)),\"\")","v":"Austin"},{"$f":"IFERROR(INDEX(Arrays!D$3:D$7,MATCH($A6,Arrays!$A$3:$A$7,0)),\"\")","v":47478},{"$f":"IFERROR(INDEX(Arrays!E$3:E$7,MATCH($A6,Arrays!$A$3:$A$7,0)),\"\")","v":4444444444}]
["Karen Fairchild",{"$f":"IFERROR(INDEX(Arrays!B$3:B$7,MATCH($A7,Arrays!$A$3:$A$7,0)),\"\")","v":"78 Westchester Ave"},{"$f":"IFERROR(INDEX(Arrays!C$3:C$7,MATCH($A7,Arrays!$A$3:$A$7,0)),\"\")","v":"Port Chester"},{"$f":"IFERROR(INDEX(Arrays!D$3:D$7,MATCH($A7,Arrays!$A$3:$A$7,0)),\"\")","v":75073},{"$f":"IFERROR(INDEX(Arrays!E$3:E$7,MATCH($A7,Arrays!$A$3:$A$7,0)),\"\")","v":5454545454},null,"References worksheet \"Arrays\" the cell in the range that will be entered.",null,null,"References worksheet \"Arrays\" the cells that contain the reference range for the formula."]
["Dynamic Table 2"]
["Name","Street","City ","Zip Code","Phone Number"]
["Jack Henry",{"$f":"IFERROR(INDEX(Arrays!B$10:B$14,MATCH($A10,Arrays!$A$10:$A$14,0)),\"\")","v":"9 Laurel Lane"},{"$f":"IFERROR(INDEX(Arrays!C$10:C$14,MATCH($A10,Arrays!$A$10:$A$14,0)),\"\")","v":"Austin"},{"$f":"IFERROR(INDEX(Arrays!D$10:D$14,MATCH($A10,Arrays!$A$10:$A$14,0)),\"\")","v":77777},{"$f":"IFERROR(INDEX(Arrays!E$10:E$14,MATCH($A10,Arrays!$A$10:$A$14,0)),\"\")","v":7777777777}]
["Mary Martin",{"$f":"IFERROR(INDEX(Arrays!B$10:B$14,MATCH($A11,Arrays!$A$10:$A$14,0)),\"\")","v":"24 East Chase Parkway"},{"$f":"IFERROR(INDEX(Arrays!C$10:C$14,MATCH($A11,Arrays!$A$10:$A$14,0)),\"\")","v":"Alberta"},{"$f":"IFERROR(INDEX(Arrays!D$10:D$14,MATCH($A11,Arrays!$A$10:$A$14,0)),\"\")","v":12128},{"$f":"IFERROR(INDEX(Arrays!E$10:E$14,MATCH($A11,Arrays!$A$10:$A$14,0)),\"\")","v":8888888888},null,"$A3",null,null,"0)),\"\")"]
["Mark Anthony",{"$f":"IFERROR(INDEX(Arrays!B$10:B$14,MATCH($A12,Arrays!$A$10:$A$14,0)),\"\")","v":"54 Lake Avenue"},{"$f":"IFERROR(INDEX(Arrays!C$10:C$14,MATCH($A12,Arrays!$A$10:$A$14,0)),\"\")","v":"Houston"},{"$f":"IFERROR(INDEX(Arrays!D$10:D$14,MATCH($A12,Arrays!$A$10:$A$14,0)),\"\")","v":56568},{"$f":"IFERROR(INDEX(Arrays!E$10:E$14,MATCH($A12,Arrays!$A$10:$A$14,0)),\"\")","v":3333333333}]
["Karen Fairchild",{"$f":"IFERROR(INDEX(Arrays!B$10:B$14,MATCH($A13,Arrays!$A$10:$A$14,0)),\"\")","v":"898 Long View Lane"},{"$f":"IFERROR(INDEX(Arrays!C$10:C$14,MATCH($A13,Arrays!$A$10:$A$14,0)),\"\")","v":"Charlotte"},{"$f":"IFERROR(INDEX(Arrays!D$10:D$14,MATCH($A13,Arrays!$A$10:$A$14,0)),\"\")","v":12587},{"$f":"IFERROR(INDEX(Arrays!E$10:E$14,MATCH($A13,Arrays!$A$10:$A$14,0)),\"\")","v":3232656599},null,"This is the cell the formula uses as a reference to get the information.",null,null,"This will put a zero when the cells are blank in the row from the reference cell and leave blank if the reference cell is blank."]
["Mary Martin",{"$f":"IFERROR(INDEX(Arrays!B$10:B$14,MATCH($A14,Arrays!$A$10:$A$14,0)),\"\")","v":"24 East Chase Parkway"},{"$f":"IFERROR(INDEX(Arrays!C$10:C$14,MATCH($A14,Arrays!$A$10:$A$14,0)),\"\")","v":"Alberta"},{"$f":"IFERROR(INDEX(Arrays!D$10:D$14,MATCH($A14,Arrays!$A$10:$A$14,0)),\"\")","v":12128},{"$f":"IFERROR(INDEX(Arrays!E$10:E$14,MATCH($A14,Arrays!$A$10:$A$14,0)),\"\")","v":8888888888}]
["Dynamic Table 3"]
["Name","Street","City ","Zip Code","Phone Number"]
["Mary Martin",{"$f":"IFERROR(INDEX(Arrays!B$17:B$21,MATCH($A17,Arrays!$A$17:$A$21,0)),\"\")","v":"21 Palmer Street"},{"$f":"IFERROR(INDEX(Arrays!C$17:C$21,MATCH($A17,Arrays!$A$17:$A$21,0)),\"\")","v":"Des Moines"},{"$f":"IFERROR(INDEX(Arrays!D$17:D$21,MATCH($A17,Arrays!$A$17:$A$21,0)),\"\")","v":25971},{"$f":"IFERROR(INDEX(Arrays!E$17:E$21,MATCH($A17,Arrays!$A$17:$A$21,0)),\"\")","v":6666666666},null,"NOTE:  The dollar sign before the character or number locks that so when copying the cell to other cells it remains the same."]
[null,{"$f":"IFERROR(INDEX(Arrays!B$17:B$21,MATCH($A18,Arrays!$A$17:$A$21,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(Arrays!C$17:C$21,MATCH($A18,Arrays!$A$17:$A$21,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(Arrays!D$17:D$21,MATCH($A18,Arrays!$A$17:$A$21,0)),\"\")","v":""},{"$f":"IFERROR(INDEX(Arrays!E$17:E$21,MATCH($A18,Arrays!$A$17:$A$21,0)),\"\")","v":""}]
["Susan Donovan",{"$f":"IFERROR(INDEX(Arrays!B$17:B$21,MATCH($A19,Arrays!$A$17:$A$21,0)),\"\")","v":"232 Yorkshire Street"},{"$f":"IFERROR(INDEX(Arrays!C$17:C$21,MATCH($A19,Arrays!$A$17:$A$21,0)),\"\")","v":"London"},{"$f":"IFERROR(INDEX(Arrays!D$17:D$21,MATCH($A19,Arrays!$A$17:$A$21,0)),\"\")","v":"235-23235"},{"$f":"IFERROR(INDEX(Arrays!E$17:E$21,MATCH($A19,Arrays!$A$17:$A$21,0)),\"\")","v":"1-99965656666"}]
["Jack Henry",{"$f":"IFERROR(INDEX(Arrays!B$17:B$21,MATCH($A20,Arrays!$A$17:$A$21,0)),\"\")","v":"5 Spring Avenue"},{"$f":"IFERROR(INDEX(Arrays!C$17:C$21,MATCH($A20,Arrays!$A$17:$A$21,0)),\"\")","v":"Dallas"},{"$f":"IFERROR(INDEX(Arrays!D$17:D$21,MATCH($A20,Arrays!$A$17:$A$21,0)),\"\")","v":99999},{"$f":"IFERROR(INDEX(Arrays!E$17:E$21,MATCH($A20,Arrays!$A$17:$A$21,0)),\"\")","v":9999999999},null,"NOTE:  Press \"Ctrl+Shift+Enter\" to lock the array before dragging to other cells."]
["Karen Fairchild",{"$f":"IFERROR(INDEX(Arrays!B$17:B$21,MATCH($A21,Arrays!$A$17:$A$21,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(Arrays!C$17:C$21,MATCH($A21,Arrays!$A$17:$A$21,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(Arrays!D$17:D$21,MATCH($A21,Arrays!$A$17:$A$21,0)),\"\")","v":0},{"$f":"IFERROR(INDEX(Arrays!E$17:E$21,MATCH($A21,Arrays!$A$17:$A$21,0)),\"\")","v":0}]