Finding specific instance of a value searched

[Dubey7]

Hi Guys, am new to this forum, trying to better my excel skills. Need help on one of my reporting file. I have one base file which i prepare for my forecasting, post which i copy the date to the actual reporting file or Input file which i publish. Issue on the file is when i copy the data from my base file to the Input file, i have multiple criteria for the same ID, For example an ID 1000012 can have a production amt and also a non production amt and also can have a deletion amt. So when i try to do a vlookup from my base file, i get the total of all (prod amt, non prod amt etc) for a particular ID. I have tried the pivot approach but its tedious since i have around 4000 lines and around 800 ID's. Further i would have new ID adding up every month as per criteria (prod, Non Prod, deletion, Open) in addition to the existing ID's. Can someone help me get this in order where i can just put a formula on my Input file to get all data month wise as per ID, Criteria (Prod, Non Prod, deletion etc..), Onshore/ offshore, Region, Vertical. If this formula can help me with getting the exiting ID's data would also help since i can manually add up the new ID's data. I know this is tricky, i have tried putting the query in the best way i could, am enclosing the file for reference where am trying to get the data for Mar month. In case need additional info, please feel free to ask. Thanx in advance.

[azumi]

Can you put the expected results (with manually input) in your file?

[Dubey7]

Sure, please check Input tab where am pulling the data from Base file tab. For the month of Mar, you can see i have input the vlookup, but the issue is vlookup takes the first matching value as per the cell mentioned. This would not solve my purpose as i have to also populate amt for Non Prod, Pipeline and Deletions for the same ID in case there is Non prod or Deletions or pipeline amt. So have to do manual inputs for adding ID and type details EVERY month for the ID's as per Base file. I would need my output based on ID, Type, Onshore/Offshore, Account, Region, After updating all the ID's i run a pivot on the Input file and Base file to cross check the amt as per, ID, type, Onshore/ Offshore, Account, Region. This takes a lot of my productive time since i have 800+ ID's and i find it difficult to add line items based on thee above mentioned criteria. Please help, Thx n advance. Enclosing file.

[JohnTopley]

Please see attached. Note I have changed format of MARCH to Date i.e 01/03/2014 (do this for all dates in both sheets)

[JohnTopley]

P.S. Formula needs to entered with Ctrl+Shift+Enter (array formula)

[Dubey7]

Thanks a Ton, that works, one more thing, in case i need to also match Onshore/Offshore, Account Name, Region & Vertical, along with the ID, Type and date which you have included in formula, how can i add them. I tried tweaking the formula you have applied, somehow not getting it rite. Kindly help on this. Thanks in advance.

[JohnTopley]

Looking at the data, I cannot see how you would do the analysis with other parameters if you also included ID as a search parameter. The output would be completely different to the current.

For example, account TCQ has multiple IDs so you need a list of unique account names against which to search.

Perhaps you could post a sample(s) of the expected output for these new tables.

[Dubey7]

Hi, my apologies, was out of town, could not reply back earlier. Well, the reason for me asking about multiple criteria was for my final review file. You are rite, the output would be different. But they are no unique ID's as such for an account, other than the ID series which rolls up under each particular account. For example TCQ Account has the ID series 1000010 to 1000012. But we do not have a unique ID as such for each account which actually complicates things for me. I tried setting up this earlier with my sourcing folks, did not work though. So have to match the exact ID as per production or non production etc for getting the output.
I can get the desired output with the Index and Match which you have suggested, but i would still have to check for new ID's which are not there in my Input file but are there on the Base file which i would to add manually. It would be really helpful in case you can suggest an approach for catching the new ID's other than running a cross VLOOKUP approach on both the files which am currently following. Thanks in advance. You have been helpful in reducing my workload.

[Dubey7]

Hi guys, can someone help, need guidance to catch new ID's as per my prev comments, thanks.