Admittedly, I'm a vlookup beginner, but I can google like no one's business and this is eluding me!
I have a monthly itemized excel list that comes in and I want to aggregate the info into one sheet. Example, I get May's info, paste it into May's tab and it automatically fills in what I need on the overall 2021 tab. I need the vlookup to scan for the store # and lot size each month and update the amount due. I can get it to scan for the store number, but if I have two lines of store # but the lot sizes are different, it populates only the first line item into the second item. I've tried vlookup and sumifs together and that doesn't seem to work. Any thoughts? Sample attached!
I've attempted the following formulas, yet somehow, the store 523 always seems to be left out for January
=IFERROR(VLOOKUP(A2&B2,Jan!A:D,4,FALSE),"")
=VLOOKUP(A2&B2,CHOOSE({1,2},Jan!A2:A9&Jan!B2:B9,Jan!C2:C9),2,FALSE)
=VLOOKUP(A2&B2,CHOOSE({1,2},B2:B9&C2:C9&D2:D9),2,0)
Hi Loki,
Welcome to our forum! The problem you're having using formulas is that your data is not set up in the correct layout to support them. You should always store your source data in a Tabular Layout. You can use Power Query to consolidate the data across the multiple sheets into a tabular layout.
You can then summarise it with a PivotTable. To update the query and the PivotTable, you just click the Refresh All button twice (once for the query and once for the PivotTable). See example file attached.
Mynda
Oh, wow! Thank you. I'm going to review it and it looks great, but... I'm not that advanced and I have peers that will use this sheet that aren't even at my level, so it needs to be simplified if possible. Is it not possible to use the style of the sheet I originally attached?
Yes, you can use the layout as you have it. The query will automatically pick up any new data as long as it's stored in the Excel Tables in each month's sheet.
Mynda
