Vlookup formula not returning values and index/match N/A# error

[Mdyson Vic]

I have a excel 2010 workbook which contains a master list and 4 weekly report tabs. The master list has all purchasers, item codes, item names and individual column totals for weeks 1 thru 4. While, the weekly report contains consistent column names of: purchaser, item code, item name, total, qty, cost and dept id. But the rows in the weekly report are not consistent.

I want the master list to display the totals specified for that week and for "0" to appear if there is no TTL that corresponds to that item. A regular v-lookup will not give me the correct results because the rows are inconsistent every week. In the master list, in the column of week 010614, i placed the formula of =if(iserror(vlookup(b7,’week010614’!b6:d8,3,false)),0,vlookup(b7,’week010614’!b6:d8,3,false)). The formula appears to be placing the zeros in the right place when there is no TTL that corresponds to that item for that week but when there is a TTL I’m getting #N/A.

My second dilemma is: when I receive the weekly report I have to manually put who the purchaser is. When I created an vlookup formula (=vlookup(b6,’masterlist!’a6:d65,1,false) to auto populate the purchaser it’s gave me an circular reference error message then #N/A appeared in the cell. Below is a sample of the workbook and the error.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Master Summary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WorkBook[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year 2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prch[/TD]
[TD]ItCd[/TD]
[TD]ItNa[/TD]
[TD]1/6/14[/TD]
[TD]1/13/14[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD]001[/TD]
[TD]Pencil[/TD]
[TD]0[/TD]
[TD]N/A#[/TD]
[/TR]
[TR]
[TD]Devon[/TD]
[TD]002[/TD]
[TD]Stapler[/TD]
[TD]N/A#[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Maya[/TD]
[TD]003[/TD]
[TD]Ruler[/TD]
[TD]N/A# [/TD]
[TD]N/A# [/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]004[/TD]
[TD]Binder[/TD]
[TD]0[/TD]
[TD]N/A# [/TD]
[/TR]
[TR]
[TD]Evans[/TD]
[TD]005[/TD]
[TD]Disks[/TD]
[TD]N/A# [/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]006[/TD]
[TD]Folders[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]007[/TD]
[TD]Phone[/TD]
[TD]0[/TD]
[TD]N/A# [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Week 010614 Tab[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year 2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supply Rpt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prch [/TD]
[TD]ItCd[/TD]
[TD]ItNa[/TD]
[TD]ttl[/TD]
[TD]qty[/TD]
[TD]ct[/TD]
[TD]dept id[/TD]
[/TR]
[TR]
[TD]N/A# [/TD]
[TD]002[/TD]
[TD]Stapler[/TD]
[TD]1[/TD]
[TD]GD[/TD]
[TD]PR[/TD]
[TD]FN[/TD]
[/TR]
[TR]
[TD]N/A# [/TD]
[TD]003[/TD]
[TD]Ruler[/TD]
[TD]2[/TD]
[TD]EX[/TD]
[TD]PR[/TD]
[TD]HR[/TD]
[/TR]
[TR]
[TD]N/A# [/TD]
[TD]005[/TD]
[TD]Disks[/TD]
[TD]6[/TD]
[TD]NJ[/TD]
[TD]TJ[/TD]
[TD]IT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is there a way for vlookup or a index/match formula that can extract accurate data for the master list, if there are inconsistent rows in the weekly report and for the purchaser in the weekly report to popular with out error?

No matter what I try nothing seems to work. Hope this makes sense, any insight will be greatly appreciated, Thank you.

 

[AliGW]

Much easier to help if you provide a sample workbook via a DropBox link or similar.

 

[Mdyson Vic]

Thank you, i redid the entire spreadsheet and changed the placement of column A1 of the masterlist. The formulas now work.