Creating a formula to look up values

[arunj]

Hi,

In my spreadsheet all the cells in column A contain the name of an inventory item and the unique inventory code number for that item. The inventory code number ranges from 0001 to 9999.

I am trying to create a formula that performs the following operation:

IF CELL A1 contains 0001 then divide cell B1 by x, IF CELL A1 contains 0002 then divide cell B1 by y, IF CELL A1 contains 0003 then divide cell B1 by z and so on all the way up to 9999.

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[arunj]

I have provided an example of what I am trying to achieve in Cell X7.

[JohnTopley]

I created a lookup table in Z2:AA5 contain Code and its factor


in X7

=IFERROR(W7/INDEX($AA$1:$AA$5,(SUMPRODUCT(ISNUMBER(SEARCH($Z$2:$Z$5,A7))*ROW($Z$2:$Z$5)))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down

[arunj]

Thank you for your prompt reply. The formula you provided does exactly what I needed. I do have some follow up questions however I would like a few days to experiment with the formula first.

[arunj]

Could you provide a formula that satisfies the condition described in cell Y7 of the attached worksheet?

[jeffreybrown]

Might this satisfy your Y7 conditions...

=IF(X7<>"",IF(X7<0,0,IF(X7>=0,ROUNDUP(X7,0))),"")

[arunj]

Thank you. The formula you provided does what was required.

[JohnTopley]

Beaten to it!

[arunj]

Thank you John. The formula you provided works.

[jeffreybrown]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[arunj]

I have one further question. Should I post here or start a new thread?

[arunj]

Every week I generate a consolidated inventory report which is formatted as displayed in the attachment with file name "datasourceformat.xls". Is it possible to create a worksheet that I can direct to source the data from the current weeks consolidated inventory report to generate the data in column A and column Y in the attached file ConsolidatedInventoryUsage-1.xlsx?

[jeffreybrown]

Sounds like a totally different question, so please close this thread as solved and start a new one. Thanks!

[arunj]

Ok. Thank you.