How do I populate a column based on whether it matches another column or not?

0

Say I have the following table:

ID  Item
1   Dog
2   Cat
3   Cat
4   Table
5   Dog
6   Chair
7   Lion
8   Lamp
9   Lion
10  Table
11  Chair
12  Lamp

Now, I want to add a third column for the category of Item. So, I made the following categorization rules:

Dog     Animal
Cat     Animal
Lion    Animal
Table   Thing
Chair   Thing
Lamp    Thing

How do I populate the third column using these categorization rules? The end result should be the following:

ID  Item    Category
1   Dog     Animal
2   Cat     Animal
3   Cat     Animal
4   Table   Thing
5   Dog     Animal
6   Chair   Thing
7   Lion    Animal
8   Lamp    Thing
9   Lion    Animal
10  Table   Thing
11  Chair   Thing
12  Lamp    Thing

I tried using INDEX-MATCH like so, but it didn't work:

enter image description here

Kristada673

Posted 2019-02-22T02:10:16.097

Reputation: 105

Answers

2

Flip your ranges, the first range is the return value and the second is the search value:

=INDEX(F:F,MATCH(B2,E:E,0))

Since your lookup is on the left you can also use VLOOKUP

=VLOOKUP(B2,E:F,2,FALSE)

Scott Craner

Posted 2019-02-22T02:10:16.097

Reputation: 16 128