If statement across MS Excel sheets

0
votes

BACKGROUND (simplified version): I’m sorting data sheets of different items for our lab. I have one sheet that lists the code number of the item (column A) with the actual name of the item (column B), for example

code | item

001 | Banana

002 | Cabbage

003 | Carrot

004 | Peach

and another sheet (labelled stocks) that lists only the code number (column A) and the amount of stock we have for that item (column B), for example

code | stock

001 | 5

002 | 6

003 | 2

004 | 7

PROBLEM: Now, these items can be sorted into 2 categories, “fruits” and “vegetables”. I can easily sort the items in the item sheet manually because I can see the actual item name, but for the stock sheet, only the code number is listed which means I would have to keep looking at the other sheet to see which item is associated with a particular code.

I already have separate item sheets for fruits and vegetables, so I have three sheets total now: fruits, vegetables, and stocks.

I was thinking of an IF statement to be placed in the stocks sheet that would indicate if the item is a fruit or a vegetable. Something like: if the code in A1 of the “stocks” sheet is present in column A of the “fruit” sheet, then print the word “fruit” in C1 of the “stocks” sheet. If not, print the word “vegetable” in C1.

My ultimate goal (what the lab is looking for) is that I would have separate item sheets for fruits and vegetables (already done) and also separate stock sheets for fruits and vegetables (to be done).

Would appreciate any help. Many thanks

2
arraysexcelif-statementexcel-formula
What is your ultimate goal here? Simply show fruit or vegetable on the Stocks sheet, or you want to do some COUNTIF or SUMIF based on that info? Besides you do not need to have two separate sheets for fruits and vegetables but a helper table indicating the product type for each product name and code, and then use VLOOKUP to look up the product type by referencing the product code on your Stocks sheet if that's all you want. - Terry W

2 Answers

0
votes

VLOOKUP works well for this.

Make a third column in the stock sheet with the following formula in Cell C2 and copy it down to the cells below:

=VLOOKUP(A2,Fruits!$A$2:$B$200,2,FALSE)

This takes the value in cell A2, find the same id in the table in sheet 1 and returns the value that is given in the 2nd column of the Fruit Worksheet.

If you want the tables fruits and vegetables separate. You can use IFNA to find the id in the second worksheet as followed:

=IFNA(VLOOKUP(A2,Fruits!$A$2:$B$200,2,FALSE),VLOOKUP(A2,Vegetables!$A$2:$B$200,2,FALSE))
0
votes

if the code in A1 of the “stocks” sheet is present in column A of the “fruit” sheet, then print the word “fruit” in C1 of the “stocks” sheet. If not, print the word “vegetable” in C1.

This is a simple test across the worksheet. This formula searches range A:A in the "Fruit" sheet, and if there is a match (returned as a number) it gives the first value "Fruit", otherwise it will return "Vegetable".

=IF(ISNUMBER(MATCH(A1,Fruit!A:A,0)),"Fruit","Vegetable")

However be careful with this, because if the code is wrong of not found, it will return "vegetable" by default. To get around this, use a nested IF statement which checks both sheets like so, and returns "Not found" if the code is wrong.

=IF(ISNUMBER(MATCH(A1,Fruit!A:A,0)),"Fruit",IF(ISNUMBER(MATCH(A1,Vegetable!A:A,0)),"Vegetable","Not found"))

EDIT: I've swapped the SEARCH for the more powerful MATCH since this will negate display options and still find the correct sheet.