MATCH

3 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.