VLOOKUP

5 posts

Finding the Largest Negative Number in Excel

If you have a list of descending values and want to find the position of the largest negative number, how would you do it?

First of all, when talking about negative numbers, which number is greater and which is lesser? Is -8 greater than -3, or is it less than -3? The answer is -8 is less than -3. So when finding the largest negative number, the number closer to zero is greater than the number further from zero.

To sum it up, -3 is larger than -8. Keeping this in mind will help when trying to find the largest negative number in a list.

A question to ask is: When would you want to find the position of the largest negative number in a descending list?

When Would You Want to Find the Largest Negative Number?

Imagine that you have a starting balance that is reduced each week, and you want a formula to calculate what week the balance ends up being below zero. This would be the week that has the largest negative number. Now this can change – the starting balance could be different; the amount it is reduced each week could be different.

See the example below:

excel largest negative number

So you want to find the week number for the largest negative number. You can see in the example image that this is Week 17.

But now, how would you use a formula to find it automatically?

What Formula Can Find the Largest Negative Number?

The formula would need to ensure that all variables of are handled. The starting balance could change; the amount the balance is reduced each week could vary; the value that you want returned is to the left of the value you are checking. So a standard VLOOKUP wouldn’t work.

How can this be handled? The formula to calculate would include four separate functions – INDEX, MATCH, LARGE, and COUNTIF. And here it is:

=INDEX(A:A, MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0))

Now that’s a lot of functions to combine, and the formula can be complex at first glance. However, breaking it makes it simpler and easier to understand what’s happening. Lets’s start breaking down the formula.

COUNTIF(B:B,”>=0″)

This function counts the number of numbers in all of column B that are greater than or equal to zero. This seems counter-intuitive since we want the largest number that is less than zero. But hold on…we’ll get to that later.

Using the data above, this function returns the value of 16, since there are 16 numbers in column B that are greater than or equal to zero.

LARGE(B:B,COUNTIF(B:B”>=0″)

This function returns the nth largest number in column B. The nth number that the LARGE function is being told to return is the result of the above COUNT function, which in this example, is 16.

The 16th largest number in column B is 5. Still not the number we are looking for – that comes next.

+1

This takes the number returned from the above COUNTIF function and adds one to it. Why do this?

Remember that the result of the COUNTIF function was 16, and this number was passed to the LARGE function. Without adding 1 to the 16 that is being passed to the LARGE function, the LARGE function would take the list of of descending numbers in column B and find the 16th largest number that is greater than or equal to zero.

That is not what is wanted. What is actually wanted is the first negative number (the greatest negative number).

That is why 1 is added to the 16. Since, in the list of descending numbers, there are 16 numbers greater than or equal to zero, the next largest number would be the largest negative number.

And that is the number that we want! In this example, the 17th largest number is -28.

Now, to get the matching week name. The example shows that the week name is in cell A18 and is Week17. Since the week name is in the column to the left of the searched value in column B, a standard VLOOKUP function won’t work. A combination of INDEX and MATCH will allow the value to the left to be returned.

MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0)

Here the MATCH function takes the result of the functions above, which is -28. It then looks in column B for that number and returns its position. The match type argument is 0. This tells the function that an exact match is wanted. The -28 in column B is in position 18.

Lastly, the resulting 18 is passed to the INDEX function part of the formula.

INDEX(A:A, MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0))

The index function now looks in the 18th cell of column A and returns that value, which is:

Week17

Here you have seen how to use a formula in Excel to find the largest negative number. While this formula uses four different functions, and does seem complex, it is very flexible.

How to Use the Excel MATCH Function [In Detail]

The MATCH function is part of Excel’s group of lookup functions. This includes INDEX, LOOKUP, VLOOKUP, and HLOOKUP.

Excel’s MATCH function lets you tell it what item to look for in a range, and it then returns the position of that item in the range. Note that it returns, not the item, but the position of the item in the range. This is different from the LOOKUP functions.

Again, it’s pretty simple and basic. However, it’s a powerful function that can be combined with the INDEX function to create very advanced lookup formulas. Here’s how to use it.

 

Start With the Details

Here’s the Excel MATCH function:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value = the value you want to find. It’s required.

lookup_array = the range of cells to search for the value. It’s required.

[match type] = tells the function how it matches the lookup_value in lookup_array. It’s optional. If you use it, you pass the values of either -1, 0, or 1 to the function.

match_type valueDescription
-1Return the smallest value that is greater than or equal to the lookup_value. The data in lookup_array must be in ascending order.
0Return the first value that is equal to the lookup_value.
1Return the largest value that is less than or equal to the lookup_value. The data in lookup_array must be in descending order.

 

Using the Excel MATCH Function

The sample below will be used as the basis for the examples in this post. Using text for the lookup_value as well as using values for the lookup_value will be shown.

excel match function

Using Text for the Lookup_value

With the above example, enter the following formula:

=MATCH(“Green”, A2:A6).

This will return the result of “3”.

The above formula is looking for the position of the text “Green” in the area of cells A2 to A6. Since the text “Green” is the third cell, the result of “3” is returned.

The match is not case-sensitive, and will return the position, even you enter “0” as the match type.

Also, if you use text for the lookup_value, and also use “0” for the match_type argument, wildcard characters of question mark (?) and asterisk (*) can be used as part of the lookup_value. If you actually want to search for a question mark or asterisk, you will need to type a tilde character (~) before it.

For example, using the formula:

=MATCH(“Ye*”, A2:A6,0),

will return “2” because “Yellow” is in position 2 in the lookup_array range.

Changing the formula to:

=MATCH(“Bl*”, A2:A6,0),

will return “4”. Note in the sample, there are two values in the lookup_array range that contain start with “Bl”. These are both “Blue” in position 4 and “Black” in position 5. Since the “0” match_type argument will return the first result that matches, it returns “4”, since “Blue” is the first match.

This may result in unintended results, so you may have to change the lookup_value criteria to be more specific, depending on your requirements.

Using Values for the Lookup_value

Using the same sample data, enter the following formula:

=MATCH(80, C2:C6).

This will return the result of “4”.

The above formula is looking for the position of the value “80” in the area of cells C2 to C6. Since the value of “80” is the fourth cell, the result of “4” is returned.

Now, change the above formula to this:

=MATCH(79, C2:C6).

This will return the result of “3”.

The above formula is looking for the position of the value “79” in the area of cells C2 to C6. Note that there is no value of “79”.

As there is no entry for the match_type argument, the default match_type of “1” is used. The argument of “1” tells the function to find the largest value that is less than or equal to “79”. In the range above, that value of “50” is in the third cell, so the result of “3” is returned.

Now, change the formula to:

=MATCH(79, C2:C6, 0).

The match_type argument of “0” tells the function to find an exact match. Since there is no exact match, the function returns the error result of “#N/A”.

How about if you enter this formula:

=MATCH(79, C2:C6, -1)?

The match_type argument of “-1” tells the function to find the smallest value that is greater than or equal to the lookup_value. Since the lookup_value is “79”, it would be reasonable to expect the function to return “4”, since the smallest value greater than or equal to “79” in the range specified is “80”, which is in position “4”.

However, the function will return the error result of “#N/A”.

This is because the values in the lookup_array range have to be in descending order if you use the match_type argument of “-1”.

Also, if you use the match_type argument of “1” to get the largest value that is less than or equal to the lookup_value, the lookup_array range values need to be sorted in ascending order.

When to use the MATCH Function?

On its own, there aren’t that many uses for the Excel MATCH function. One use of the function on its own is to use the function to have a formulaic way to verify that a value or text exists in a list.

However, when combined with other functions, MATCH becomes more useful. Such as, when combined with INDEX, it provides a powerful, and flexible replacement for VLOOKUP.

 

Using the (Simple) Excel INDEX Function

When I hear the word “Index”, I tend to think of the dictionary definition. Merriam-Webster defines Index as “a list of items (such as topics or names) treated in a printed work that gives for each item the page number where it may be found”.

For example, an index in a printed book would be a list of topics, typically in alphabetical order, and would show what page the topic would be on.

This is a fitting description for the Excel INDEX function, except that the Excel INDEX function does this in reverse. You tell the INDEX function where to look, and it returns the value in that location.

Using the example of the book from the above paragraph, you would pick a page and then list what topics are found on it.

Excel’s INDEX function lets you lookup a value based on the row and column passed to the function.

Start With the Basics

Here’s Excel’s INDEX function:

INDEX(array, row_num, [column_num])

array = a range of cells or an array. It is required.

row_num = the row in the array to return a value from. This is optionally required. See the note below.

Note: If row_num is not included, column_num must be included.

[column_num] = the column in the array to return a value from. This is also optionally required. See the note below.

Note: If col_num is not included, row_num must be included.

Additional Notes

  • If you include both row_num and column_num, the INDEX function returns the value where the two arguments intersect.
  • If you set row_num or column_num to zero, INDEX returns the value in the entire row or column.
  • If row_num or column_num refer to a location outside of array, the #REF! error will be returned.

How to Use Excel INDEX

using the excel index function

Using the above example, entering the formula:

=INDEX(A2:B6, 1, 2)

The A2:B6 tells the function to look in the range of cells from A2 to B6.

The image below highlights the area that it looks in.

excel index range
Highlighted Excel Index Range

The “1” passed to the function tells the function to look in row one of the range, which is equal to cells A2:B2.

The image below highlights the area that it looks in.

excel index row
HIghlighted Excel Index Row

Next, the “2” passed to the function tells the function to look in column two of the range, which is equal to cells B2:B6.

The image below highlights the area that it looks in.

excel index colunn
Highlighted Excel Index Column

Now, if you look at where the two red rectangles intersect, you will notice that there is one cell. This is the cell that is returned by the function.

The result of this function is the value in cell B6, which is “Large”.

excel index intersection
Highlighted Intersection of Excel Index

That’s how you use the function. Now why would you want to use it?

Why You Want to Use The INDEX Function

Now that how it works is explained, why would you want to use it?

Take a scenario where you have a list of items. You can use the INDEX function to look up a specific item in the list.

That’s about it. There’s not much more to say about this function.

But like so many of Excel’s functions, it can be combined with other functions to create powerful tools. Take, for example, combining the INDEX function with the MATCH function. These two, in combination, are an excellent replacement for the VLOOKUP function.

SUMMARY

Excel INDEX is a pretty straightforward function that does one basic task. It looks up a value based on what row and column is passed to it and returns that value.

It’s true power lies in its ability to be combined with other functions to create very powerful Excel tools.

If you have tips or comments on how you use the INDEX function, please let me know.

Harness the (Power) of Excel VLOOKUP

Excel VLOOKUP is quite possibly one of the most popular and yet misunderstood Excel functions. It is a powerful function that can be used to simplify and automate so many things in Excel.

As an Excel user advances in their skills, VLOOKUP typically becomes one of the next powerful tools used.

See how to use it, as any tool becomes more useful as the user’s knowledge and skill in manipulating it increases.

Start With the Basics

Basically, what VLOOKUP does is look in a column of a table of data for a value that you tell it to find. It then returns whatever value is in the same row of that table of data, but from a column that you tell it to look in.

The one restriction with VLOOKUP is that you can’t return a value that is to the left of the column that you are looking in. You can only return values in columns to the right.

Examine the Excel VLOOKUP Function

Here’s the function:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

  • lookup_value = the value you want to find in the table
  • table_array = the table of data that you want to look in
  • col_index_num = the column in the table of data that you want the value returned to you
  • range_lookup = logical value. If you want to find the closest match (TRUE) or an exact match (FALSE)

See How Excel VLOOKUP Is Used

The image below is a table that will be used for the VLOOKUP function. The store number of ‘1184’ will be looked up in the table to find that store’s phone number. It’s in cell A4 and highlighted in a red box.

vlookup-table-highlighted-value
Table of Data for VLOOUP

This is the formula that will be used to get the phone number for that store number using VLOOKUP:

=VLOOKUP(1184,A2:C5,2,FALSE)

The result of that formula will be (555) 555 6492, which is the value in cell B4.

Let’s break the formula down into plain English:

“Using the data in cells A2 through C5, find the exact match of the value ‘1184’ in left-most column. Once you find ‘1184’ in that column (which is always column 1), show the value in column 2 of that same row.”

The Real Power of VLOOKUP

In the example above, the value of “1184” was hard-entered into the formula. That was just done for the sample.

The real power of VLOOKUP is that you can pass a cell value, a formula result, or the result of a function to VLOOKUP.

Imagine that you have a table of data with those store numbers. It has hundreds, thousands, or even more rows. You want to know the address and phone number for each of those rows.

Just like this sample that has only a few rows:

data-needing-excel-vlookup
Sample Data Needing VLOOKUPs

All that you need to do is add two columns to the data – one for the address, and one for the phone number.

Now, add the VLOOKUP formula to those two columns, and instead of hard-entering the value that you want to look up, enter the cell address that has the store number.

This is what it will look like:

excel-vlookup-formula
The VLOOKUP Formulas

Copy that formula down in each column, using this technique, and you now have all of that information available.

Note, as seen in the formulas above, that you will need absolute references to the cells that are the data table if you want to use AutoFill to copy the formulas down. This ensures that each formula looks in the same, correct range of cells.

Here is the result:

excel-vlookup-result
Result of Adding VLOOKUPs

Conclusion

VLOOKUP is a great time saver that allows you to lookup values in a cell in a table of data and pull another value from the same row that it found the data in. The resulting data can also be used in a PivotTable. The possibilities are nearly endless.

With the PowerPivot feature being added to Excel you have an option to VLOOKUPs. See this post to read more about PowerPivot and how to use it instead of VLOOKUP.

Let me know if you have any tips or questions related to using the VLOOKUP function.

Excel PowerPivot As VLOOKUP

Who doesn’t love to use VLOOKUP?  Using that formula saves both time and effort when you need to analyze data.

However, if you have thousands or more of VLOOKUP formulas, a performance hit to your computer’s CPU can happen. Not only that, the addition of one or more columns of data will be needed, depending on how many VLOOKUP formulas are used. Also, formulas can break when you add to, remove from or move columns or rows in the source data.

This is where using Excel PowerPivot as VLOOKUP will really help.

To use PowerPivot, you will need to enable the tab. If you don’t have the PowerPivot tab in your Excel, see this post to enable the tab.

First Step: Getting Data Into PowerPivot

PowerPivot needs Excel 2010 or newer. With PowerPivot, Excel tables, and database tables can become part of the PowerPivot.

There is more than one way to get the data into PowerPivot. The Excel data can be copied and pasted into PowerPivot as a table, or an Excel table can be linked to PowerPivot.

Copying and pasting will produce a static table in PowerPivot. However, linking an Excel table to a PowerPivot table will allow any changes made to the data in Excel to automatically be updated in PowerPivot.

Next Step: Using PowerPivot as VLOOKUP

In the example used for this post, what is wanted is a summary of the total amount per account code description. Yet, there are two separate tables of data – one with the account code description and the other without it.

One table has the sales information, with the account code, but no description of that account code that the sales are made to.

The second table has the account code and the description.

Typically, to get the description, a column would be added to the first table of data with a VLOOKUP formula to pull the description field from the second table of data.

However, by using PowerPivot as VLOOKUP, there will be no need to add a column or create a formula.

Below are the two tables from one spreadsheet that will be used for the PowerPivot.

powerpivot as vlookup
First Table to be Joined by PowerPivot

 

powerpivot as vlookup
Second Table to be Joined by PowerPivot

To link an existing Excel table to PowerPivot, click in the table and then on the PowerPivot tab, under Tables, click Add to Data Model.

powerpivot as vlookup
PowerPivot Tab Add to Data Model Button

After adding both tables to the data model in PowerPivot, select the home tab, and then click diagram view.

You will see that both table are now presented as diagrams. This diagram view look very similar to the Relationships view of data table in Microsoft Access. The same principles apply to creating relationships between the data in PowerPivot and the data in Access tables.

powerpivot as vlookup
PowerPivot Diagram View

Next, you will need to link the related fields in each table. In this case it is the ‘Account Code’ field. Click on the field name in either table and drag to the same field name in the other table.

powerpivot diagram view powerpivot as vlookup
PowerPivot Diagram View Showing Table Relationship

This creates a relationship between those two fields in both tables, effectively replacing the VLOOKUP formula, using PowerPivot as VLOOKUP.

Last Step: Create the Pivot Table

Now click on the home tab and the click PivotTable. This creates a pivot table using the tables you have just added and created a relationship between.

Notice that the fields in the pivot table are now grouped by table.

Add the description field from the account codes table to the rows area and add the total field from the expenditures table.

powerpivot as vlookup
PowerPivot Pivot Table Fields

Now the pivot table allows the viewing of the total per account code description. No columns or formulas were needed to be added to do this.

pivottable created powerpivot as vlookup
Pivot Table Created Using PowerPivot

Conclusion

PowerPivot is a very powerful tool that can be used for consolidation and reporting on large quantities of data.

This was a very simple demonstration with only only two tables. This same process can be repeated for more tables of data which wouild normally be used with more VLOOKUPS. This should help you think of ways to use PowerPivot for your own needs.

Please let me know if you have some ways of using PowerPivot for your own data and reporting needs.