We’re back with Excel tricks (does that really show I love this app?) and again with a question that usually gets pretty repetitive. It is no wonder that I have been asked on more than one occasion, both personally and at work, how to perform this operation, seemingly so simple, but requiring knowledge of the correct Excel function to perform it. out without a problem.

I mean of course, as you already saw in the title, how to check in Excel if a value is in a range. Or more precisely, to check if No the number of values ​​is in a range that can be the same for all of them or variable for each of them. We will see both cases, because although the formula is practically similar, there is a small but important detail that we need to consider in the first case: the absolute reference.

And on top of that, we’ll also try to make the value we want to check to see if it matches the range as dynamic as the current date, so that the result varies depending on when we’re checking the document. All this is, as I have already indicated, extremely simple. don’t you believe it? Well, you will check it below

variable range

In this first case, we want to check that for each employee, their current value is between a pre-defined minimum and maximum (which can also be changed if we deem it necessary). So we start with a document like this:

As you will see, each employee has a minimum and a maximum as well as a current value and a cell to the right where we will need to enter a formula that will check if the current is between the two. In the case of the first row with data 2 (the first row contains the header) we need to check if 149 is between 150 and 375 (yes, seen as an individual calculation it is very simple, but remember that we are talking about functions that allow checking hundreds or thousands of records at once ). So we want D2 to be greater than B2 and less than B3. And for that we will use the AND function.

Specifically, the function we will need to write in cell E2 is the following: «=Y(D2>=B2; D2» (without quotes, of course). The AND function allows us to perform several checks and will only return “True” if all of them are true. In this case, as you can see by parsing the formula, we check if D2 is equal to or greater than B2, and then, after the semicolon (which is the condition separator), if D2 is less than C2, limiting the range mode. . When you press Enter to verify the formula, you will see that the answer is

Then use Excel’s AutoFill feature to transfer the formula to the remaining cells where you need it (if you’re new to AutoFill, just place your mouse pointer in the lower right corner of the cell you just edited and when it changes its appearance, click and drag without releasing until all cells are selected). This applies the check to all rows

If you want, you can now use the conditional formatting features we’ll explain in this tip to more quickly visually identify items that are in and out of range.

fixed range

We are now starting from a similar document, but in which the minimum and maximum values ​​of the range are similar for all, e.g.

In this case, we will have to use a formula similar to the one in the previous example (of course we will change the origin cells of the current value, minimum and maximum), and in addition, we will have to write the dollar symbol, $, to the left of the letter and to the left of the number of the cell with the minimum and maximum value. This means that the formula for this example with the cells shown in the image will be as follows: «=AND(B2>=$F$2; B2<$F$3)» (again without quotes).

what do we do about it? Tell Excel that these references are absolute, meaning they should not change when the formula is copied to other cells. And so, if you look at the previous example, when using autofill, the formula was updated in each cell to refer to the minimum and maximum values ​​of its row. Now we don’t want that to happen because the result would be bad.

However, with an absolute reference to the min and max cells, copying the formula to the remaining cells will update the reference to the current value but preserve the cells bounding the range, giving the correct result.

Today in the time period

Now we will see a case similar to the first example, but with an interesting variation, which is that we change the numbers to dates and make the current value of all rows the same: today’s date.

To make it easier to understand, let’s assume that we have a list of employees with the start and end date of their vacation, and we want Excel to automatically tell us who is on vacation and who is not when we open the document. Of course, we could manually enter the current date for this, but it’s not really necessary because Excel has a function that gives us the specified value.

So suppose we start from a document like this

Well, as I already indicated, we will again use the AND function, but also the TODAY() function, which will always return the exact date and time. And as you can guess, what we are going to modify is a reference to the current value, which instead of the value from the table will be the information provided by the function.

So, to split the cells in the example image, the formula we’ll need to enter in D2 is the following: «=Y(TODAY()>=B2; TODAY()

But if we open the same document in a few days, for example on December 25, the result will be different

If you like Excel tricks, remember that we also recommend these three to make your life easier, and that we also told you how to calculate the direct rules of three in inversions, as well as how to calculate different types of percentages.

More information about Excel: Microsoft