Share via

if statement to return category names based on description

Anonymous
2013-04-09T18:16:16+00:00

Hi, I have a spreadsheet with over 8,000 rows of data.  In that spreadsheet, list of 34 file type descriptions which fall into one of 4 categories.  I need to start creating reports based on category.  My current spreadsheet only lists the program names.

I want to add a column called Category and then use an IF statement to return the correct category name.  Below is the list of programs and the category.

I need the IF statement to say if F2 is Commercial Media or Film*, put Media Film, or if F2 is Media Sports, Amateur Sports or Sport, put Media Sports, If F2 is Motorsports, put Motorsports, for all else put Media Live.

Thank you very much!!

File Type Category
Commercial  Media Media Film
Film Media Film
Film Completion Media Film
Film Production Media Film
Film Production Completion Media Film
Film Production Completion Guarantee Media Film
MPTV Media Film
Media Sports Media Sports
Amateur Sports Media Sports
Sports Media Sports
Motorsports Motorsports
Contingency Media LIVE
DICE Media LIVE
Entertainer Media LIVE
Equipment Rental Media LIVE
Individual Risk Media LIVE
Musical Media LIVE
Office Media LIVE
Office MLT Media LIVE
Office OLT Media LIVE
Promoter Media LIVE
Recording Studio Media LIVE
Rental Media LIVE
Rental House Media LIVE
Rental Housing Media LIVE
Shell Media LIVE
Shell Corp Media LIVE
Social Services Media LIVE
Special Events & Leisure Media LIVE
Theatrical Media LIVE
Touring Media LIVE
Touring Entertainer Media LIVE
TULIP Media LIVE
Venue Media LIVE
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2013-04-09T18:25:24+00:00

Let's say the above table is on a sheet named Categories, in A1:B35 (with the headers File Type and Category in row 1).

The formula you need is

=VLOOKUP(F2,Categories!$A$2:$B$35,2,FALSE)

If you want to suppress the #N/A you'd get if F2 contains a program not listed in column A, you could use

=IFERROR(VLOOKUP(F2,Categories!$A$2:$B$35,2,FALSE),"")

or

=IFERROR(VLOOKUP(F2,Categories!$A$2:$B$35,2,FALSE),"Unknown")

The formula can be filled down as far as needed.

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-04-10T14:00:47+00:00

    Let's say the above table is on a sheet named Categories, in A1:B35 (with the headers File Type and Category in row 1).

    The formula you need is

     

    =VLOOKUP(F2,Categories!$A$2:$B$35,2,FALSE)

     

    If you want to suppress the #N/A you'd get if F2 contains a program not listed in column A, you could use

     

     

    =IFERROR(VLOOKUP(F2,Categories!$A$2:$B$35,2,FALSE),"")

     

    or

     

    =IFERROR(VLOOKUP(F2,Categories!$A$2:$B$35,2,FALSE),"Unknown")

     

    The formula can be filled down as far as needed.

    Worked like a charm and so much easier than using IF statements!  Thank you very much!

    Was this answer helpful?

    0 comments