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