CODEWITHSUNDEEP
stringvbaexcel
user1564762
user1564762

Reputation: 835

How to auto categorise items in Sheet2 from Sheet1 - find string

In Sheet1, column A contains the items and column B the category. So one item is associated to one category (but one category can have more then one item).

Sheet1:

Cat         Animal
Movie       Media
Music       Media
Gasoline    Car
Water       Food
Fotball     Sport
Dog         Animal

Sheet2 contains the items that are going to be categorised. I want column B in Sheet2 to be autofiled with help from Sheet1 (as much as possible).

Sheet2:

Lion
BMW
Computer
Dog
Hamburger
Water

The correct autofil would be:

Lion
BMW
Computer
Dog         Animal
Hamburger
Water       Food

Upvotes: 0

Views: 132

Answers (1)

WGS
WGS

Reputation: 14179

Unless I'm missing something from the above, the following formula in Sheet2 B1 will suffice:

=VLOOKUP(A1,Sheet1!A:B,2,FALSE)

... copied down.

Set-up:

Sheet1:

enter image description here

Result:

enter image description here

If you don't want the N/A error to appear in case the item has no matching category, use the following variation:

=IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),"")

Result:

enter image description here

Simple application and description of the formula can be found here.

Hope this helps.

Upvotes: 2

Related Questions