How to match terms from multiple columns to one column using VBA

0
votes

I have Sheet1 of User ID's associated with multiple terms. The terms are listed in separate columns. There are up to 30 terms for each user. The sheet is organized as User ID in column A and Terms in Columns B,C,D, etc.

I then have a series of Terms associated with a unique Categories in Sheet2 (Term in column A, Category in Column B). The goal is to match up users with categories. So, I need to perform a Vlookup of epic proportions or run a VBA program that compares the terms in columns B,C,D, etc in Sheet1 to the Terms in Column A in Sheet2.

If found in Sheet2 it would be great if the formula replaced the term with the associated category in Sheet1.

1
excelvlookupvba

1 Answers

4
votes

Create a sheet3

paste the following into sheet3, cell A1:

=iferror(vlookup(sheet1!A1,sheet2!:$A:$B,2,0),sheet1!A1)

Then drag down and to the right for as many rows / columns that were in sheet1.

Essentially, the formula will lookup whatever is in each sheet1 cell in the sheet2 list. if it's found it returns the value and if it isn't it just shows what was on sheet1.

Update

If you have an older version of excel that doesnt have iferror you can use iserror like this:

=if(iserror(vlookup(sheet1!A1,sheet2!:$A:$B,2,0)),sheet1!A1,vlookup(sheet1!A1,sheet2!:$A:$B,2,0))