Excel - IF Function Problem - Expert Solution

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
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.