Range contains specific text

Add business days to date

=WORKDAY(start_date,days)

If you need to add or subtract business days (workdays) to a date so that you can calculate a date in the future or past that skips weekends (and holidays), you can use the WORKDAY function.

How this formula works

If you have a starting date of Tue Jan 5, 2016, and you want to calculate a date 5 business days into the future, you can use this formula:

=WORKDAY(A1,5) // returns Tue, Jan 12, 2016

The WORKDAY function automatically skips weekends. To also skip holidays, just include a reference to a range that includes the dates that holidays occur on:

=WORKDAY(start_date,days,holidays)

For example, if you have a list of holiday dates in the range H1:H15, and you want to calculate a date 10 business days after the date in A1, excluding both weekends and holidays, use this formula:

=WORKDAY(A1,10,H1:H15)

Convert date to text

=TEXT(date,format)

If you need to convert a date to a string (a text value) you can easily do so with the TEXT function. Just supply the date and your preferred number format (i.e. "dd/mm/yyyy").

How this formula works

Dates and times in Excel are stored as serial numbers and converted to human readable values on the fly using number formats. When you enter a date in Excel, you are able to apply a number format to display that date as you like. In a similar way, the TEXT function allows you to convert a date or time into text in a preferred format. For example, if the date January 9, 2000 is entered in cell A1, you can use TEXT to convert this date into the following text strings as follows:

=TEXT(A1,"mmm") // "Jan"

=TEXT(A1,"dd/mm/yyyy") // "09/01/2012"

=TEXT(A1,"dd-mmm-yy") // "09-Jan-12"

Assuming a date of January 9, 2012, here is a more complete set of formatting codes for date, along with sample output.

Format code	Output
d	9
dd	09
ddd	Mon
dddd	Monday
m	1
mm	01
mmm	Jan
mmmm	January
mmmmm	J
yy	12
yyyy	2012
mm/dd/yyyy	01/09/2012
m/d/y	1/9/12
ddd, mmm d	Mon, Jan 9
mm/dd/yyyy h:mm AM/PM	01/09/2012 5:15 PM
dd/mm/yyyy hh:mm:ss	09/01/2012 17:15:00

You can use TEXT to convert dates or any numeric value in a fixed format. You can explore available formats by navigating to Format Cells (Win: Ctrl + 1, Mac: Cmd + 1) and selecting various format categories in the list to the left.

Exact match lookup with INDEX and MATCH

{=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num)}

Case-sensitive lookup

By default, standard lookups with VLOOKUP or INDEX + MATCH aren't case-sensitive. Both VLOOKUP and MATCH will simply return the first match, ignoring case.

However, if you need to do a case-sensitive lookup, you can do so with an array formula that uses INDEX, MATCH, and the EXACT function.

In the example, we are using the following formula

{=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3)}

This formula is an array formula and must be entered with Control + Shift + Enter.

How the formula works

Since MATCH alone isn't case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but they way we use it is a little unusual, because we need to compare one cell to a range of cells.

Working from the inside out, we have first:

EXACT(F4,B3:B102)

where F4 contains the lookup value, and B3:B102 is a reference to the lookup column (First names). Because we are giving EXACT an array as a second argument, we will get back an array of TRUE false values like this:

{FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,etc.}

This is the result of comparing the value in B4 every cell in the lookup column. Wherever we see TRUE, we know we have an exact match that respects case.

Now we need to get the position (i.e. row number) of the TRUE value in this array. For this, we can use MATCH, looking for TRUE and set in exact match mode:

MATCH(TRUE,EXACT(F4,B3:B102),0)

It's important to note that MATCH will always return the first match in there are duplicates, so if there happens to be another exact match in the column, you'll only match the first one.

Now we have a row number. Next, we just need to use INDEX to retrieve the value at the right row and column intersection. The column number in this case is hard-coded as 3, since the named range data includes all columns. The final formula is:

{=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3)}

We have to enter this formula as an array formula because of the array created by EXACT.

This formula will retrieve both text and numeric values. If you want to retrieve only numbers, you can use a formula based on SUMPRODUCT; see link below