Vlookup or Any function to trace variable location

[Poh Hong Tar]

I have problems with excel lookup function.

My job is generating the Cycle Count List when doing the stock take. The problem is this item (Material Code) could be have variable locations in the 1st place. Another problem is variable quantities also. Eg as per below:

Material Code Location Qty
ABC 1BA01A01 30
DEF 1BA01A02 40
ABC 1BA02A01 70
ABC 1BA02A02 40
DEF 1BA03A01 70

Do anyone can post out the solution to do the lookup formula or another function except pivot table and subtotal due to i need to do data matching and generating the discrepancies list to solve this?

Thanks in advance for anyone here and sorry for my poor english writing skill.

 

[NARAYANK991]

Hi ,

Can you upload a workbook with the data ?

From your post , I don't think there should be any problem in using the VLOOKUP function.

Narayan

 

[Poh Hong Tar]

Hi Narayank,

Sure no problem.

 

[NARAYANK991]

Hi ,

Your uploaded workbook contains some data in one tab named Master List ; what do you want to do with this data ?

It is still not clear what you wish to do. Is your workbook incomplete ?

Narayan

 

[Poh Hong Tar]

Hi Narayan,

My uploaded workbook contains some data in one tab named Master List is this could be Computer generated inventory balance report. I am intend to add new worksheet as cycle count list and inside should be Material code and Location only so i need to a lookup function to check the qty by one material code and different locations.

Sorry for my incomplete workbook and the inconvenience caused.

 

[NARAYANK991]

Hi ,

See if this is what you wanted.

Narayan

 

[Poh Hong Tar]

Hi Narayank,

Thank you very much and this what i want. Sorry for late reply due to heavy volume for daily workload