Jump to content


Photo

Creating Automatic Categories

If Function? Boolean Logic? IF OR VLOOKUP

  • Please log in to reply
1 reply to this topic

#1 Mike Loomis

Mike Loomis

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 23 August 2016 - 10:56 PM

Hello,

 

I am trying to categorize a very large number of accounts into three categories. The account numbers have five or six digits. The categories will be Labor, Misc. Labor, and Non-Labor. The first digit of all the accounts that will makeup the Labor category is 1. The first digit of the accounts that will makeup the Misc. Labor category is 2. The first digit of the accounts that will makeup the Non-Labor category start with 3. There is one exception, which is there is one account that starts with the digit 2 that will go into the Labor section. I want to categorize the accounts in a column. 

 

I used a left formula to capture the first digits of the accounts. What I am not sure about is how would I combine that type of formula with a formula or function that handles the exception. I thank you in advance. I have a good reason not to use a V-Lookup. 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 24 August 2016 - 03:57 PM

Hi Mike,

 

This sounds like a perfect case for the IF function. You could use the IF function to always assign your 'special case' to the Labor category and then either use VLOOKUP or additional IF functions for all other cases.

 

You said you have a good reason not to use VLOOKUP, but it would make for a much more compact and readable formula. With VLOOKUP, your formula would look something like this:

=IF([First Digit]=2,"Labor",VLOOKUP([Last Digit],Category,2,FALSE))

 

This formula models the following logic:

If the first digit is 2, show Labor, otherwise use VLOOKUP to find the category based on the last digit.

 

You can see an in-depth explanation of how the IF function works in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

For instructions on how to use the VLOOKUP function, see the video tutorial: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

Without VLOOKUP, you would have to use a series of nested IF functions to check for each category individually. This means it would be a much more complicated formula like this:

=IF(OR([First Digit]=2,[Last Digit]=1),"Labor",IF([Last Digit]=2,"Misc. Labor","Non-Labor"))

 

This models the logic:

If the first digit is 2 OR the last digit is 1, show Labor, otherwise if the last digit is 2, show Misc Labor, otherwise show Non-Labor.

 

You'll notice that I've used the OR function to reduce the number of IF functions that are needed. You can see more about how the OR function works in the video lesson: Expert Skills Lesson 3-16 Use the AND and OR functions to construct complex Boolean criteria.

 

I have attached an example workbook showing these formulas in action:

 

Attached File  IF-OR.xlsx   11.28KB   59 downloads

IF-OR.png

 

I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users