{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Welcome","Vlookup - danger, danger","Vlookup- danger 2","Vlookup 2 - approximate","HLookup","Match and index - 1","Match and index - 2","Match and index - 3","Get and Transform (VLOOKUP)"],"not_rendered":{"charts":0,"images":8,"macros":1,"external_links":2,"defined_names":4,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Welcome","index":0,"n_rows":16,"n_cols":1,"rows_kept":16,"cut":false,"hidden":false,"merged":[[5,0,11,0]]}
[]
["VLOOKUP – Excel’s most dangerous function and how to avoid it"]
[]
["Warning: This workbook is for demonstration purposes only and has not been extensively checked."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["VLOOKUP is one of Excel's most popular functions and many see its use as the mark of an Excel expert. However, VLOOKUP hides a dark secret that can lead to financial disaster and reputational catastrophe. "]
["Find out what can go wrong, how to stop it going wrong and how to use Match and Index to do more than VLOOKUP could even dream of. Then find out how to avoid lookups altogether."]
["The webinar will last 30-40 minutes and you can ask questions throughout. "]

{"sheet":"Vlookup - danger, danger","index":1,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["VLOOKUP()"]
[]
[]
["Finland",{"$f":"VLOOKUP(A4,$A$7:$B$11,2)","v":1}]
[]
["Country","Flag ID"]
["Finland",1]
["Korea (North)",2]
["Switzerland",3]
["USA",4]
["Korea (South)",5]

{"sheet":"Vlookup- danger 2","index":2,"n_rows":12,"n_cols":9,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["VLOOKUP()  - inserted columns"]
[]
["Check:",{"$f":"SUM(TrialBalance6[Amount])","v":-98000},null,null,null,null,null,{"$f":"SUM(CodeChart5[Total])","v":0},{"$f":"IF(B3=H3,\"OK\",B3-H3)","v":-98000}]
[]
["Code","Amount","Category","Category (MATCH)",null,"Code","Category","Total"]
[0,-98000,{"$f":"VLOOKUP(A6,CodeChart5[],2,TRUE)","v":"Sales"},{"$f":"INDEX(CodeChart5[],MATCH(TrialBalance6[Code],CodeChart5[Code],1),MATCH(TrialBalance6[[#Headers],[Category]],CodeChart5[#Headers],0))","v":"Sales"},null,0,"Sales"]
[null,null,null,null,null,100,"Cost of sales"]
[null,null,null,null,null,200,"Distribution"]
[null,null,null,null,null,300,"Administration"]
[null,null,null,null,null,400,"Financial"]
[null,null,null,null,null,500,"Taxation"]
[null,null,null,null,null,"Total",null,{"$f":"SUBTOTAL(109,CodeChart5[Total])","v":0}]

{"sheet":"Vlookup 2 - approximate","index":3,"n_rows":45,"n_cols":9,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["VLOOKUP() - approximate match"]
[]
["Check:",{"$f":"SUM(TrialBalance[Amount])","v":-232800},null,null,null,null,null,{"$f":"SUM(CodeChart[Total])","v":0},{"$f":"IF(B3=H3,\"OK\",B3-H3)","v":-232800}]
[]
["Code","Amount","Category",null,null,"Code","Category","Total"]
[0,-98000,{"$f":"VLOOKUP(A6,CodeChart[],2,TRUE)","v":"Sales"},null,null,0,"Sales"]
[1,-100000,{"$f":"VLOOKUP(A7,CodeChart[],2,TRUE)","v":"Sales"},null,null,100,"Cost of sales"]
[2,-120000,{"$f":"VLOOKUP(A8,CodeChart[],2,TRUE)","v":"Sales"},null,null,200,"Distribution"]
[3,-115000,{"$f":"VLOOKUP(A9,CodeChart[],2,TRUE)","v":"Sales"},null,null,300,"Administration"]
[4,-132000,{"$f":"VLOOKUP(A10,CodeChart[],2,TRUE)","v":"Sales"},null,null,400,"Financial"]
[5,-200000,{"$f":"VLOOKUP(A11,CodeChart[],2,TRUE)","v":"Sales"},null,null,500,"Taxation"]
[10,-150000,{"$f":"VLOOKUP(A12,CodeChart[],2,TRUE)","v":"Sales"},null,null,"Total",null,{"$f":"SUBTOTAL(109,CodeChart[Total])","v":0}]
[11,-152000,{"$f":"VLOOKUP(A13,CodeChart[],2,TRUE)","v":"Sales"}]
[12,-146000,{"$f":"VLOOKUP(A14,CodeChart[],2,TRUE)","v":"Sales"}]
[13,-195000,{"$f":"VLOOKUP(A15,CodeChart[],2,TRUE)","v":"Sales"}]
[14,-160000,{"$f":"VLOOKUP(A16,CodeChart[],2,TRUE)","v":"Sales"}]
[20,-36000,{"$f":"VLOOKUP(A17,CodeChart[],2,TRUE)","v":"Sales"}]
[21,-42000,{"$f":"VLOOKUP(A18,CodeChart[],2,TRUE)","v":"Sales"}]
[22,-43200,{"$f":"VLOOKUP(A19,CodeChart[],2,TRUE)","v":"Sales"}]
[23,-17500,{"$f":"VLOOKUP(A20,CodeChart[],2,TRUE)","v":"Sales"}]
[24,-26000,{"$f":"VLOOKUP(A21,CodeChart[],2,TRUE)","v":"Sales"}]
[50,-79500,{"$f":"VLOOKUP(A22,CodeChart[],2,TRUE)","v":"Sales"}]
[60,-36200,{"$f":"VLOOKUP(A23,CodeChart[],2,TRUE)","v":"Sales"}]
[100,350800,{"$f":"VLOOKUP(A24,CodeChart[],2,TRUE)","v":"Cost of sales"}]
[110,220100,{"$f":"VLOOKUP(A25,CodeChart[],2,TRUE)","v":"Cost of sales"}]
[120,96000,{"$f":"VLOOKUP(A26,CodeChart[],2,TRUE)","v":"Cost of sales"}]
[130,210000,{"$f":"VLOOKUP(A27,CodeChart[],2,TRUE)","v":"Cost of sales"}]
[200,120000,{"$f":"VLOOKUP(A28,CodeChart[],2,TRUE)","v":"Distribution"}]
[210,56000,{"$f":"VLOOKUP(A29,CodeChart[],2,TRUE)","v":"Distribution"}]
[220,13000,{"$f":"VLOOKUP(A30,CodeChart[],2,TRUE)","v":"Distribution"}]
[230,7000,{"$f":"VLOOKUP(A31,CodeChart[],2,TRUE)","v":"Distribution"}]
[240,0,{"$f":"VLOOKUP(A32,CodeChart[],2,TRUE)","v":"Distribution"}]
[250,2300,{"$f":"VLOOKUP(A33,CodeChart[],2,TRUE)","v":"Distribution"}]
[260,15800,{"$f":"VLOOKUP(A34,CodeChart[],2,TRUE)","v":"Distribution"}]
[270,6900,{"$f":"VLOOKUP(A35,CodeChart[],2,TRUE)","v":"Distribution"}]
[300,340000,{"$f":"VLOOKUP(A36,CodeChart[],2,TRUE)","v":"Administration"}]
[310,120000,{"$f":"VLOOKUP(A37,CodeChart[],2,TRUE)","v":"Administration"}]
[320,23000,{"$f":"VLOOKUP(A38,CodeChart[],2,TRUE)","v":"Administration"}]
[330,5600,{"$f":"VLOOKUP(A39,CodeChart[],2,TRUE)","v":"Administration"}]
[340,11200,{"$f":"VLOOKUP(A40,CodeChart[],2,TRUE)","v":"Administration"}]
[350,3600,{"$f":"VLOOKUP(A41,CodeChart[],2,TRUE)","v":"Administration"}]
[360,27500,{"$f":"VLOOKUP(A42,CodeChart[],2,TRUE)","v":"Administration"}]
[370,11800,{"$f":"VLOOKUP(A43,CodeChart[],2,TRUE)","v":"Administration"}]
[400,-42000,{"$f":"VLOOKUP(A44,CodeChart[],2,TRUE)","v":"Financial"}]
[450,17000,{"$f":"VLOOKUP(A45,CodeChart[],2,TRUE)","v":"Financial"}]

{"sheet":"HLookup","index":4,"n_rows":13,"n_cols":6,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
[]
[]
["Type",33329,35977,39783,40179,40547]
["Standard",0.17499999999999999,0.17499999999999999,0.15,0.17499999999999999,0.2]
[]
[]
["Type","Date","VAT rate","Net","Gross"]
["Standard",40909,{"$f":"HLOOKUP(Table77[[#This Row],[Date]],$B$3:$F$4,2,TRUE)","v":0.2},100,{"$f":"ROUND(D8*(1+C8),2)","v":120}]
["Standard",39813,{"$f":"HLOOKUP(Table77[[#This Row],[Date]],$B$3:$F$4,2,TRUE)","v":0.15},30,34.5]
["Standard",39813,{"$f":"HLOOKUP(Table77[[#This Row],[Date]],$B$3:$F$4,2,TRUE)","v":0.15},70,80.5]
["Standard",39782,{"$f":"HLOOKUP(Table77[[#This Row],[Date]],$B$3:$F$4,2,TRUE)","v":0.17499999999999999},80,94]
["Standard",40209,{"$f":"HLOOKUP(Table77[[#This Row],[Date]],$B$3:$F$4,2,TRUE)","v":0.17499999999999999},200,235]
["Standard",40211,{"$f":"HLOOKUP(Table77[[#This Row],[Date]],$B$3:$F$4,2,TRUE)","v":0.17499999999999999},105,123.38]

{"sheet":"Match and index - 1","index":5,"n_rows":10,"n_cols":5,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["MATCH() and INDEX() - intersection"]
[]
[null,null,"Jan","Feb","Mar"]
[null,"America",50171,48243,29344]
[null,"Asia",68199,73336,88220]
[null,"Europe",63075,70171,74833]
[null,"UK",55273,25642,75783]
[]
[null,null,"Feb"]
[null,"Asia",{"$f":"INDEX($B$3:$E$7, MATCH(B10,$B$3:$B$7,), MATCH(C9,$B$3:$E$3,))","v":73336}]

{"sheet":"Match and index - 2","index":6,"n_rows":14,"n_cols":7,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["MATCH() and INDEX() - smallest larger"]
[]
["Next event:",{"$f":"D7","v":"From IF to Infinity and beyond"}]
[]
[]
[null,null,null,"Course"]
["Today",{"$f":"NOW()","v":"2016-02-04T13:04:58.050"},{"$f":"MATCH(B7,G11:G14,-1)","v":1},{"$f":"INDEX(B11:B12,C7,1)","v":"From IF to Infinity and beyond"}]
[]
["Schedule"]
["Venue","Course","Date","Start","Finish","am/pm","Until time"]
["Webinar","From IF to Infinity and beyond",42423,"16:30:00","17:30:00",{"$f":"D11","v":0.6875},{"$f":"C11+E11+(1/24)","v":"2016-02-23T18:30:00"}]
["Webinar","VLOOKUP - Excel's most dangerous function",42403,"16:30:00","17:30:00",{"$f":"D12","v":0.6875},{"$f":"C12+E12+(1/24)","v":"2016-02-03T18:30:00"}]
["Webinar","Data analysis in Excel 2016",42388,"16:30:00","17:30:00",{"$f":"D13","v":0.6875},{"$f":"C13+E13+(1/24)","v":"2016-01-19T18:30:00"}]
["Webinar","Excel 2016 - what's in it for me",42347,"16:30:00","17:30:00",{"$f":"D14","v":0.6875},{"$f":"C14+E14+(1/24)","v":"2015-12-09T18:30:00"}]

{"sheet":"Match and index - 3","index":7,"n_rows":18,"n_cols":5,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
[]
[]
["Type","Exempt","Zero","Standard","Reduced"]
[33329,0,0,0.17499999999999999,0.17499999999999999]
[35977,0,0,0.17499999999999999,0.05]
[39783,0,0,0.15,0.05]
[40179,0,0,0.17499999999999999,0.05]
[40547,0,0,0.2,0.05]
[]
[]
[]
["Type","Date","VAT rate","Net","Gross"]
["Standard","2012-01-01",{"$f":"INDEX(VATRates[#All], MATCH(B13,VATRates[[#All],[Type]],1), MATCH(A13,VATRates[#Headers],0))","v":0.2},100,{"$f":"ROUND(D13*(1+C13),2)","v":120}]
["Exempt","2008-12-31",{"$f":"INDEX(VATRates[#All], MATCH(B14,VATRates[[#All],[Type]],1), MATCH(A14,VATRates[#Headers],0))","v":0},30,30]
["Standard","2008-12-31",{"$f":"INDEX(VATRates[#All], MATCH(B15,VATRates[[#All],[Type]],1), MATCH(A15,VATRates[#Headers],0))","v":0.15},70,80.5]
["Standard","2008-11-30",{"$f":"INDEX(VATRates[#All], MATCH(B16,VATRates[[#All],[Type]],1), MATCH(A16,VATRates[#Headers],0))","v":0.17499999999999999},80,94]
["Reduced","2010-01-31",{"$f":"INDEX(VATRates[#All], MATCH(B17,VATRates[[#All],[Type]],1), MATCH(A17,VATRates[#Headers],0))","v":0.05},200,210]
["Standard","2010-02-02",{"$f":"INDEX(VATRates[#All], MATCH(B18,VATRates[[#All],[Type]],1), MATCH(A18,VATRates[#Headers],0))","v":0.17499999999999999},105,123.38]

{"sheet":"Get and Transform (VLOOKUP)","index":8,"n_rows":45,"n_cols":6,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
["Code","Amount",null,null,"Code","Category"]
[0,-98000,null,null,0,"Sales"]
[1,-100000,null,null,100,"Cost of sales"]
[2,-120000,null,null,200,"Distribution"]
[3,-115000,null,null,300,"Administration"]
[4,-132000,null,null,400,"Financial"]
[5,-200000,null,null,500,"Taxation"]
[10,-150000]
[11,-152000]
[12,-146000]
[13,-195000]
[14,-160000]
[20,-36000]
[21,-42000]
[22,-43200]
[23,-17500]
[24,-26000]
[50,-79500]
[60,-36200]
[100,350800]
[110,220100]
[120,96000]
[130,210000]
[200,120000]
[210,56000]
[220,13000]
[230,7000]
[240,0]
[250,2300]
[260,15800]
[270,6900]
[300,340000]
[310,120000]
[320,23000]
[330,5600]
[340,11200]
[350,3600]
[360,27500]
[370,11800]
[400,-42000]
[450,17000]