Question description:
This user has given permission to use the problem statement for this
blog.
Hi, I am trying to do some price logging for company bills. I am trying to match a specific item code to its price, but am having trouble with using formulas. For example.. If an item is "MS425-16-HW" then it costs 1099.00. There are a number of different orders so implementing formulas will save me a lot of time.
Solved by B. B. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
11/07/2018 - 01:32
Hi
Excelchat Expert
11/07/2018 - 01:32
Hello, I understand that you need help with a formula to automatically lookup price, right?
User
11/07/2018 - 01:33
I was trying to use an IF function but I cannot figure it out
User
11/07/2018 - 01:33
I am entering information for each individual office BOM
User
11/07/2018 - 01:33
so there is a lot of repetition
Excelchat Expert
11/07/2018 - 01:33
There's a better approach to this but it would really help me a lot if you could show me how your data structure looks like.
User
11/07/2018 - 01:34
I am unable to share the document as it is against company policy
User
11/07/2018 - 01:34
I will try to verbalize
Excelchat Expert
11/07/2018 - 01:34
Instead of verbalizing, it would be easier if you could just create a sample data.
User
11/07/2018 - 01:34
Ah ok
User
11/07/2018 - 01:34
how do I do that
Excelchat Expert
11/07/2018 - 01:35
Do you see the document to the right?
User
11/07/2018 - 01:35
I will be requesting access from pwc email
Excelchat Expert
11/07/2018 - 01:35
If you don't, then you can edit your file and remove all private information.
User
11/07/2018 - 01:35
I just requested access to the document
Excelchat Expert
11/07/2018 - 01:35
You should not need to do that.
Excelchat Expert
11/07/2018 - 01:36
If you are seeing that screen then I'm afraid you don't have access to the file and I can't grant you access from my end as it is the company that operates that account.
Excelchat Expert
11/07/2018 - 01:36
But you could just create a mock data in a new worksheet.
Excelchat Expert
11/07/2018 - 01:36
A very simple one and then upload it here using the clip icon next to this chat.
User
11/07/2018 - 01:37
Any excel doc will be encrypted, let me make a google doc and send you a link
Excelchat Expert
11/07/2018 - 01:37
Sure, make sure the google doc is open to everyone.
User
11/07/2018 - 01:39
The settings only allow pwc members to see
User
11/07/2018 - 01:39
Is it impossible to discuss verbally
Excelchat Expert
11/07/2018 - 01:39
Unfortunately, we can only discuss via this chat.
User
11/07/2018 - 01:39
That is fine
Excelchat Expert
11/07/2018 - 01:40
You can try to explain it via chat but I can only provide a solution based on how you explain it and how I understood it.
User
11/07/2018 - 01:41
I basically just need to make it so when column B "Product code" is MS425-16-HW then column E "Cost per item" is automatically populated at 14,000
User
11/07/2018 - 01:41
MR42-HW to 1099
Excelchat Expert
11/07/2018 - 01:41
Are there any other combinations?
User
11/07/2018 - 01:42
There are probably 12-15 items
Excelchat Expert
11/07/2018 - 01:42
Hmm. Okay. If you have that much combination then it will have to be a lookup table.
Excelchat Expert
11/07/2018 - 01:42
Is it okay to add a new sheet in your file?
User
11/07/2018 - 01:42
Yes it is
Excelchat Expert
11/07/2018 - 01:43
Okay, before I try, let me first send you blank file to see if you will be able to download it.
User
11/07/2018 - 01:43
I wont be able to
Excelchat Expert
11/07/2018 - 01:44
Okay, then I'll have to instruct you with a step by step procedure.
Excelchat Expert
11/07/2018 - 01:44
What row does your data start in column B?
Excelchat Expert
11/07/2018 - 01:45
Still with me?
Excelchat Expert
11/07/2018 - 01:48
Here are the steps:
Excelchat Expert
11/07/2018 - 01:48
Create a new sheet, name it Lookup
Excelchat Expert
11/07/2018 - 01:48
In A1 of that cell type MS425-16-HW
Excelchat Expert
11/07/2018 - 01:48
In B1 type 14,000
Excelchat Expert
11/07/2018 - 01:48
In A2 type MR42-HW
Excelchat Expert
11/07/2018 - 01:48
B2 type 1099
Excelchat Expert
11/07/2018 - 01:49
And just keep adding your data downwards.
Excelchat Expert
11/07/2018 - 01:49
Now in E2 of the original sheet, apply this formula:
Excelchat Expert
11/07/2018 - 01:49
=vlookup(B2,lookup!A:B,2,0)
Excelchat Expert
11/07/2018 - 01:50
This formula will check the value of B2 and check it against the Lookup sheet.
Excelchat Expert
11/07/2018 - 01:50
If it finds the value of B2 it will return the adjacent price.
Excelchat Expert
11/07/2018 - 01:50
You can then drag the formula from E2 downwards and it will automatically apply to all your values in column B.
Excelchat Expert
11/07/2018 - 01:51
If you need to add / remove or edit new prices or BOM, you can easily do that in Lookup and your data in the original sheet will automatically update.
Excelchat Expert
11/07/2018 - 01:51
Our session is about to end and I won't be able to stop it unless you extend the session.
Excelchat Expert
11/07/2018 - 01:51
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
