How to use the CONCATENATE Function and '&' Operator to Join Text or Numbers or Dates

Hi friends,

In this post I'll discuss how to concatenate text strings, cells, ranges, columns and rows in Excel using the CONCATENATE function and "&" operator step-by-step.

YouTube Video Links :

Concatenate Part 1       Concatenate Part 2       Concatenate Part 3

Download Excel File 

This topic is very interesting as I hope you know CONCATENATE function more or less, but I'll show you some quick methods and magical tricks to use this function very fast.

In practical situations Excel workbook's data is not always structured according to your needs and you may want to organize it, combine data from two or more columns into a single column. Common examples that require concatenation in Excel are joining names and address parts, combining text with a formula-driven value, displaying dates and times in the desired format etc.

So, let's learn how to use CONCATENATE step-by-step in much easier way.

Syntax 

CONCATENATE(text1, [text2], …)

Where text is a text string, cell reference or formula-driven value.

The simplest CONCATENATE formula to combine the values of cells A1 and B1 is as follows:

=CONCATENATE(A1, B1)

Concatenating a text string and cell value

There is no reason for the Excel CONCATENATE function to be limited to only joining cells' values. You can also use it to concatenate various text strings to make the result more meaningful.

For example:

=CONCATENATE(A2," ",B2," ",C2," ",D2," Concatenate Test")

How to use CONCATENATE function

To get the function either you can type =CONCATENATE() or you can click in formula bar to open the argument box as shown above, please drag the slide bar in this little box if you find that the arguments are not enough for your data.

Please notice that we add a space before the word " Concatenate Test" to separate the concatenated text strings.

Naturally, you can add a text string in the beginning or in the middle of your Concatenate formula as well:

A space (" ") is added in between the combined values, so that the result displays as ” E148 Sam T-137 50 Concatenate Test"

Concatenating a text string and a formula

To make the result returned by some formula more understandable for your users, you can concatenate it with a text string that explains what the value actually is.

For example, you can use the following formula to return the current date:

=CONCATENATE(D16," ",TEXT(E16,"dd-mmm-yy"))

Here again we use one function within another i.e., nested function, Test().

The syntax of Text function is =TEXT(value, format_text). 

We gave the reference "E16" to value parameter and "dd-mmm-yy" to format_text, so that we get the date as DD-MMM-YY format.

How to use CONCATENATE function with Date

Things to Remember

a)    In a single CONCATENATE formula, you can concatenate up to 255 strings, a total of 8,192 characters.

b)    The result of the CONCATENATE function is always a text string, even when all of the source values are numbers.

c)    Excel CONCATENATE does not recognize arrays (i'll show you how to use array in CONCATENATE function). Each cell reference must be listed separately. 

For example, 

you should write 

=CONCATENATE(A1, A2, A3) instead of =CONCATENATE(A1:A3).

"&" operator to concatenate strings in Excel

In Microsoft Excel, '&' operator is another way to concatenate cells. This method come in very handy in many scenarios because typing the ampersand (and) sign (&) is much quicker than typing the word "concatenate" .

Similarly to the CONCATENATE function, you can use "&" in Excel to combine different text strings, cell values and results returned by other functions.

For Example

To see the concatenation operator in action, let's re-write the CONCATENATE formulas discussed above:

Concatenate the values in A1 and B1:

=A1&B1 in place of =CONCATENATE(A1, B1)

Difference Between Excel "&" operator and CONCATENATE function...

The only essential difference between CONCATENATE and "&" operator is the 255 strings limit of the Excel CONCATENATE function and no such limitations when using the ampersand. Otherwise, there is no difference between these two concatenation methods, nor is there any speed difference between the CONCATENATE and "&" formulas.

How To Use The TRANSPOSE function to get the range in ARRAY. (Array is nothing but collection of numbers or strings, etc.)

When you need to concatenate a huge range consisting of hundreds of cells, the above method is not fast enough because it requires clicking on each cell. In this case, a tricky & better way is to use the TRANSPOSE function to return an array, and then replace it with individual cell references in one fell swoop.

1.    Select the cell where you want to output the concatenated range.

2.    Enter the TRANSPOSE formula in that cell,       =TRANSPOSE(A1:A10) in this example.

3.    In the formula bar, press F9 to replace the formula with calculated values.

4.    Delete the curly braces that turn a usual Excel formula into an array formula. As a result, you will have all the cells references to be included in your concatenation formula.

Type =CONCATENATE( in front of the cell references in the formula bar, type the closing parenthesis and press Enter.

CONCATENATE and ARRAY

Hope you enjoined this post for further clarification please watch my YouTube video, it will be more clear.... 

How to use the COUNTIFS Function

Friends, today I am going to show you how to use Microsoft Excel COUNTIFS step-by-step which works fast.

Download Link : MS Excel Download File
YouTube Link : How to use COUNTIFS Step-by-step

Description

The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets a single or multiple criteria.

The details are given in the following paragraphs but to make it easy let us first focus on the Syntax

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n])

By supplying the parameters you can get the count the data based upon your Criteria, more than one. Its very simple but useful function, so, lets discuss how to use it. Criteria is for which data you want to count, Criteria Range is the range where this data exists.

In COUNTIF you get only one range and one Criteria but in COUNTIFS you will get numerous Criteria and Criteria range to select the data from multiple conditions.

Parameters or Arguments

criteria_range1

The range of cells that you want to apply criteria1 against.

criteria1

The criteria used to determine which cells to count. criteria1 is applied against criteria_range1.

criteria_range2, ... criteria_range_n

Optional. It is the range of cells that you want to apply criteria2, ... criteria_n against. There can be up to 127 ranges.

criteria2, ... criteria_n

Optional. It is used to determine which cells to count. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.

  As you can notice from the below image (second table) that I need the count of T-67 in two rows in two contions - 

1. Location Code T-67 & Employee Code E140
2. Location Code T-67 & Name is Shanta

Excel COUNTIFS function

And the formula here is : 

1. Location Code T-67 & Employee Code E140 -- "=COUNTIFS(A1:A14,A17,C1:C14,B17)"

1. Location Code T-67 & Name is Shanta -- "=COUNTIFS(C2:C14,B18,B2:B14,C18)"

One more thing please don't forget to protect your formula using Dollar $ sign.

How to use INDEX function with MATCH function, replace VLOOKUP, speed up processing time

Hi Friends,

Today I will show you how to use INDEX function with MATCH function instead of using VLOOKUP step-by-step. 

Free Download INDEX() MATCH() MATCH() Microsoft Excel File

Now the important question that comes first in our mind, "Why should I not use VLOOKUP ?"

Obviously VLOOKUP is the most useful and easy function to use. If VLOOKUP is used with other functions i.e., nested with other function its data extracting power increases very high and to some extent we can make it dynamic (using MATCH in place of col_index_num which I've explained and shown in my Youtube video, please refer to VLOOKUP with MATCH function).

Although VLOOKUP has great advantages and uses in Microsoft Excel based data but in some situations there is requirement of some tweak in VLOOKUP or the VLOOKUP function is not sufficient.

Why INDEX MATCH function combined more beneficial than VLOOKUP is a different question. You can easily found numerous sites explaining this in www.google.com, therefore, I'll not discuss it here.

The only thing is the processing time, the processing time in VLOOKUP is very high as compared to INDEX & MATCH function as because VLOOKUP searches the LOOKUP_VALUE in the whole range provided but when INDEX() MATCH() MATCH() function is used it only searches through the specified ROWs and COLUMNs, and therefore, the processing time is relatively less. 

In practical situation you can feel this while dealing with large vloumns of data in Microsoft Excel. You'll not feel this difference while using it in small data.

Now let's explain how to use INDEX(), MATCH(),  MATCH() in Microsoft Excel.

Before going to the nested formula lets analyze INDEX() function first.

The Syntax of INDEX() is :

INDEX(array, row_num, [column_num])

As the parameters are now common to us, we'll move further. 

Array is the Table, actually array is "a set of organized data", it might be a row or a column or both. Arraysmay of multi-dimentional (We'll not discuss here).

Now as the syntax of INDEX() shows, we can supply the array and can provide the row number and column number and it'll display the data in the position provided by (row, column), as the below picture shows.

How to use INDEX function

Now if we look at the Syntax of MATCH() function, it looks like....

MATCH(Lookup_value, lookup_array, [match_type])

Here, in this MATCH() function we provide the lookup value, lookup value an the match type (which is usually '0' for Exact match) then, it'll show a number. Now, we'll use it in INDEX() function's second and third parameter as shown in the below picture. And it's done.

How to use INDEX function

Now if we look at the final nested function's Syntax it becomes ...

INDEX(array, MATCH(Lookup_value, lookup_array, [match_type])

, MATCH(Lookup_value, lookup_array, [match_type]))

The first MATCH() function in red color is for ROW number calculations and the second MATCH() in brown color is for COLUMN number calculations.

Now in example file it looks like 

=INDEX(Sheet1!$A:$T,MATCH(Sheet2!$B8,Sheet1!$A:$A,0),MATCH(Sheet2!$C$7,Sheet1!$A$1:$Y$1,0))

and the picture is ...

How to use INDEX and MATCH function

There are three video for the support of this blog by me, you can watch it in the below links.

1. INDEX() & MATCH() Function Part 1

2. INDEX() & MATCH() Function Part 2

3. INDEX() & MATCH() Function Part 3

OR 

How to use the VLOOKUP Function with Match Function- Advanced Part 4 & 5

In this article I will show you the use of MATCH function with VLOOKUP function step-by-step.

Click here to Download the working File

But the obvious question arises "why should I use MATCH function in VLOOKUP ?"

It is because sometimes, you will face such a situation that you have to derive lots of data in specific order from some unorganised data e.g., in the below image you can easily notice that Column Names are not organised, now if we need to get data in an organised specified format, we have to write some VLOOKUP functions (almost same) row wise side-by-side, but the count of VLOOKUPs would be equal to the count of COLUMNs.

VLOOKUP with MATCH function in Excel

Example of VLOOKUP with MATCH function in Excel

It’s a tedious and time taking job and moreover there might be chances of getting wrong data if not carefully written.

But using match function with VLOOKUP, these VLOOKUP functions will reduced to ONE!!!

Only ONE VLOOKUP is required if it is nested with match function. 

Is it not amazing ???

So, lets understand first what MATCH function does and then we will use it within VLOOKUP...

The syntax of match function is

MATCH( lookup_value, lookup_array, [match_type] )

How to use MATCH function

The output of the above formula in picture is 8., which when I counted manually is also 8.

Now, I’ll explain one by one…

Lookup_Value : Which value we want to look or data against which value we need (in my example it is Product 16)

Lookup_Array : Where is this value (lookup value) exists (from more than one value) (in my example it is A2 to N2 cells)

Match_Type : (1) - Less than, (0) - Exact match & (-1) – Greater than 

In excel whenever this function runs, it’ll display a number.

We’ll use this number in VLOOKUP’s Column Index number. The nested formula would be like…

From this => VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

To  => VLOOKUP(lookup_value, table_array, MATCH( lookup_value, lookup_array, [match_type] )

, [range_lookup])

How to use VLOOKUP with MATCH function

Yellow highlighted part in the formula is the MATCH(), look carefully to understand the total formula. For any query please post your query in the comment box, I'll help you...

If you are still facing problem to understand what I want to convey you, please go to my YouTube video please click in the below link or you may watch it here.

YouTube VIdeo Liknk 1         YouTube VIdeo Liknk 2

So what will happen within this nested function?

1.      VLOOKUP will search for lookup_value using Table_array

2.      VLOOKUP will ask the column index number to MATCH function

3.      And match function will return the column number where the specified column name exists.

4.      VLOOKUP will be closed using match_type.

How to use the VLOOKUP Function - Advanced Part 3

Friends,

I hope you have read my previous article regarding VLOOKUP. In this post I'll give you some more information about nested VLOOKUP step-by-step.

Nested is to put a function within another function (I'll discuss in detail about NESTED FUNCTION in some another post).

I'll show you nested function in VLOOKUP today. 

Click this link to download Excel FIle : Vlookup Data File

Please note the red colored formula in the below picture.

Nested VLOOKUP in Excel with COLUMNS fnction and Named Table

As you can see in my above formula, "=VLOOKUP(G5,Data1,COLUMNS(A5:E5)-2,0)" in column index number I've written another function "COLUMNS()", this is an example of nested function.

You can put COLUMN() function and upto 64 functions can be nested as per requirement.

This is to calculate the column index number, either you can put numbers like 2,3,4,5 etc., or you can use formula. I'll show you another method which I generally use. Have a look at the below image circled in Blue.

Nested VLOOKUP in Excel with COLUMNS fnction

I put column number (circled in blue) to the top of the column name, and then I refer the column index number by that number and drag it to the right as per requirement, then fridge it by pressing 'F4' button once. (Here I again changed my table name to 'Data2').
It will make the VLOOKUP very dynamic.

But have you noticed Table Array part, it is 'Data1' or 'Data2'....

What is this? Where did I get it?

How to name a table in Excel

Its the table name, Data1. You can put a name to excel table. For detail view visit my Youtube Video link.

Youtube Link - VLOOKUP advanced - 2

How ? Simple Select the range as per your requirement in excel, go to the box which displays Cell Number, type the table name, that's it. Done...

So, a table array can also be refereed by table name. I will discuss about the naming an Excel table in another article saperately.

I'll show you few easy method to calculate VLOOKUP in my next blog....

Till then, keep reading, like & share my Youtube videos.
Thank you....

How to use the VLOOKUP Function - Advanced Part 2

So far we used VLOOKUP without any dollar sign, but sometimes we need to move the formula to another cell by just copy & paste. Whenever we'll try do that this formula gets moved, therefore, we have to create a boundary to this formula and it is by using a Dollar ($) sign. You can put a doller sign by using keyboard or by pressing 'F4" key. Press this button thrice it'll show single dollar sign in front of cell definition like '$A2'.

Now we can copy and paste the formula as shown in figure, highlighted by yellow.

Now, while putting the formula in cells you might notice under range_lookup two options are coming, TRUE-Approximate match & FALSE-Exact match.

What are the uses of these two ?

Lets find it step-by-step.

As the name suggest if we select 'FALSE' it will find for EXACTLY match in the table_array, on the contrary if we select TRUE it will select APPROXIMATE  match in the table_array.

Exact match means, if this function finds an exact match, it will display a result otherwise it will give an #N/A error.

Now for TRUE part, we can use digit '1' and for FALSE part, we can use digit '0'. So the above formula becomes "=VLOOKUP($V2,$C:$D,2,0)" if we need EXACT match, and "=VLOOKUP($V2,$C:$D,2,1)" if we need approximate match.

Its very rare the we need Approximate match and it is easy to use '0' instead of FALSE while using VLOOKUP.

If you want to visit video tutorial for further clarification then please click on the following links...

How to use VLOOKUP Part 2



Or you can directly watch the video here...