Answer from last year’s blog

1. If a particular cell says “Yes”, then the output is 1, else 2,
   • =IF(B2=“Yes”,1,2)
2. If a person scores 50/100 or greater, then they receive a Pass, else they Fail,
   • =IF(B5>=0.5,“Pass”,“Fail”)
3. If a number is equal to or greater than 0, then it is positive, else it is negative,
   • =IF(ISBLANK(B8),“Enter any number >”,IF(B8>=0,“Positive”,“Negative”))
4. If a person has a credit score equal to or greater than 800, then it is Excellent.
   • =IF(ISBLANK(B11),“Enter number >”,IF(B11>=800,“Excellent”,“N/A”))
5. Continuing from 4, if a person has a credit score greater than 700, then it is Very Good, and
   • =IF(ISBLANK(B14),“Enter number >”,IF(B14>=800,“Excellent”,IF(B14>=700,“Very Good”,“N/A”)))
6. Continuing from 5, if a person has a credit score greater than 625, then it is Good. If it’s greater than 550, then it is Fair, otherwise it is Below Average.
   • =IF(ISBLANK(B17),“Enter number >”,
     IF(B17>=800,“Excellent”,
     IF(B17>=700,“Very Good”,
     IF(B17>=625,“Good”,
     IF(B17>=550,“Fair”,
     “Below Average”)))))

What IF We Did Something Else?

A post at the end of last year, I created some images that outline the IF process. When you’re using an IF function for something simple, like looking for a ‘Yes’ or ‘No, ‘1’ or ‘0’, or ‘Pass’ or ‘Fail’, then it can be pretty simple to put it together. When you’re accounting for a process that requires multiple IF conditions, it can get a bit more complicated and become pretty time-consuming. However, there is a way that we can take this complication and time-consumption and throw it out the window with another function – VLOOKUP.

What is VLOOKUP?

Vertical Lookup (VLOOKUP) is like an assistant in a data table. If there’s a piece of information that you don’t want to search through to find, VLOOKUP is the function that helps you to do this automatically.

Vertical lookup searches down the first column of a range for a key and returns the value of a specified cell in the row found.

• Search_key: The value to search for. For example, ’42’, ‘Cats’ or ‘I24’.
• Range: The range to consider for the search. The first column in the range is searched for the key specified in ‘search_key’.
• Index: The column index of the value to be returned, where the first column in ‘range’ is numbered 1.
• is_sorted – [optional]: Indicates whether the column to be searched (the first column of the specified range) is sorted, in which case the closest match for ‘search_key’ will be returned.

Here’s a spreadsheet that I used to do these exercises:

https://docs.google.com/spreadsheets/d/1WpSd-83DRZpDhwoBpLWFNS8C5pyVfJGrIex_TdYWrNY/edit#gid=1772762751

Example

Let’s say you keep track of the official NBA statistics and you get into an argument over the player who scored the most cumulative points during regular season games. If you wanted to do a search on particular players and their statistics, this is where VLOOKUP would come in.

Here is a subset of the statistics you track:

Your “friend” says that Karl Malone has scored the most points. You can use the VLOOKUP function to easily observe his total number of points. First, let’s create the results table underneath the dataset:

Next we’ll enter the VLOOKUP function:

Using the template/definitions outlined above, we are going to search:

1. The Player’s name,
2. In the dataset (C4:X13),
3. Where the results return the fourth column (points), and
4. The exact player’s name matches.

Now, we can start to enter the names of players. Since we’re testing Karl Malone, let’s put that name in and see how many points come back.

We can see that this formula has given us the number of Karl Malone’s points; 36,928. Now, let’s check LeBron James:

You knew it. You knew that your “friend” was wrong and you were right. *cough* Moving on.

Using Last Year’s Example

In that post from December, 2023, we used a series of nested IF functions to find a person’s credit score. Let’s use that same example to see how we can use VLOOKUP over NESTED IF functions. Here’s the sheet for this example: https://docs.google.com/spreadsheets/d/1WpSd-83DRZpDhwoBpLWFNS8C5pyVfJGrIex_TdYWrNY/edit#gid=939677612.

As credit scores are awarded as integers in increments of one, this means that they will need to be grouped; those who are between [0, 449], between [550, 624], [625, 699], [700, 799], and greater than 800. As scores will sit in between these groups, this will affect how we set up the dataset. It will need to be in ascending order.

Next, let’s add the formula:

Using the template/definitions outlined earlier, we are going to search:

1. The credit score,
2. In the dataset (A9:B13),
3. Where the results return the second column (category), and
4. An approximate (grouped) match; fuzzy match.

Let’s look at the different Credit Category possibilities (and feel free to follow along in the spreadsheet):

Comparison

So, as you can see, there is a massive difference between the Nested IF and VLOOKUP process. One definitely allows you to be more parsimonious and efficient regarding writing formulas.

Shortcomings

There are a few limitations that VLOOKUP has, limiting how you can use the function.

1. Can only search through data in one direction, from the first column
   1. VLOOKUP can only look through data from left to right, not right to left. This means if you’ve created a dataset, you might need to rearrange the columns to best make it work, otherwise you will need to create your own subset of the data.
      1. For example: you have a dataset with the following columns: FIRST NAME, LAST NAME, ID, ADDRESS, OCCUPATION. If you want to use the VLOOKUP function with a person’s ID, you will only be able to return their address and occupation as they’re right of the ID column. 
2. Perform searches across columns (vertical).
   1. It cannot search across rows. This is what HLOOKUP is for.
3. Only returns one (first matching) result,
   1. If there are multiple occurrences of the lookup value, it retrieves the first one it finds.
      1. For example: let’s pretend that we have multiple instance of Red in column D: =VLOOKUP(“Red”, D2:E10, 2, False). It will only return one result.
4. Inflexible “database”  range,
   1. The range specified in VLOOKUP must be static, and if you insert or delete columns in the table, you may need to manually update the range.
      1. For example: If you add a new column between columns A and B, the following formula may not work correctly: =VLOOKUP(“January”, A2:C10, 3, False)
5. VLOOKUP assumes data is sorted
   1. When using approximate match (TRUE), you need to make sure that the dataset is in ascending order, hence that step in the exercise above,
      1. For example: If the data in column A is not sorted in ascending order, the result may not be accurate; =VLOOKUP(75, A2:B10, 2, True).
6. You need to account for VLOOKUP’s default behaviour
   1. It doesn’t find an exact match by default. It will return an #N/A error. To return an approximate match using VLOOKUP, you must set the final parameter as TRUE or 1.
      1. For example: If I had entered Kobe in the first (NBA) VLOOKUP function, it would return an error and not the number of points he scored (see below for a more thorough explanation AND SOLUTION to this issue.)

To overcome some of these limitations, you can use alternative functions like INDEX and MATCH. We’ll be looking at this in a later post.

Additional Tips

VLOOKUP Wildcards

Ever heard of a wildcard? In card games, they may be used to represent any other playing card, sometimes with certain restrictions. Jokers are often used as wildcards, but other cards may be designated as wild by the rules or by agreement. In Excel, we can set up a type of wildcard action. Within the program, they are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data.

Let’s go back to the NBA example and you can’t be bothered typing in full names – rightly so. Let’s adjust the search_term portion of the formula to allow for our laziness efficiency mindset. Here, we had to type in Kobe Bryant’s full name to see the result:

Let’s type in ‘Kobe’ and see what happens:

Since Kobe is not an exact match in the dataset, it will return the logic that the entry wasn’t found. This is where we can start to play with the wildcards. Since I know that ‘Kobe’ is the start of the string, I will want Excel to account for the rest of the name that I’m not entering. So, I will enter &“*” next to C17, where “*” is the wildcard character:

=VLOOKUP(C17&“*”,C4:X13,4,0)

Now we have the Kobe results:

To drive this point of wildcards home, let’s try searching Jordan with the current cell’s formula:

This code has reported an error. Can you figure out why? This is because the wildcard is set after the search term. Jordan is the end of the string text in the Player name column, but we haven’t counted for the beginning of it. So, let’s add the wildcard character before the search term too.

Now that’s looking better. Let’s try searching for ‘Chamber’ (for Wilt Chamberlain). Hopefully this returns 31,419:

I hope this helps to show the power of wildcards in Excel.

Homework

Formulate VLOOKUP formulas for the following scenarios (the hardest part will be formulating them into tables):

1. Inventory (respective):
   1. Products: [Apple, Banana, Kiwi, Lemon]
   2. Amount: [24, 30, 0, 41]
   3. Price: [$0.52, $0.24, $0.63, $0.17]
   4. Query: when someone searches for a fruit in the inventory database, the VLOOKUP function must return whether it is in stock or not.
2. Employee Location:
   1. Name: [Jim, Pam, Michael, Toby, Scarn, Catherine]
   2. Age: [28, 27, 45, 42, 46, 35]
   3. Company: [Dunder, Mifflin, Disney, Amazon, Google, FBI]
   4. Query: Where do Michael and Scarn work?
3. Employee pay bands:
   1. Pay Band Table
      1. Pay Min: [$25,000, $50,000, $75,000, $100,000, $150,000]
      2. Pay Band: [Level A, Level B, Level C, Level D, Level E]
   2. Pay Report Table
      1. Employee ID: [360284, 239044, 309837, 380817, 225623, 637411, 897743, 297965, 309138, 722567, 904127, 733034, 101299]
      2. Pay: [$25,347, $123,597, $46,890, $143,321, $88,948, $103,154, $36,143, $81,452, $140,719, $44,368, $157,855, $73,549, $153,185]
      3. Pay Band (Query): categorise each employee to their proper payroll pay band based on the amount they are being paid.

As per usual, my answer will be in next week’s post.