CONCATENATE function in Excel.

1. Select a blank cell you will output the concatenation result, and enter the formula
=CONCATENATE(TEXT(A2, "yyyy-mm-dd")," ", B2) ( A2 is the cell with date you
will concatenate, and B2 is another cell you will concatenate) into it, and press the Enter
key.

2. Keep selecting the concatenation result cell, and drag its AutoFill handle to the
range as you need. And then you will see the specified cells are concatenated into one
cell with keeping the date format. See screenshot:
Note: This formula =CONCATENATE(TEXT(A2, "yyyy-mm-dd")," ", B2) will show
dates of any date format as yyyy-mm-dd, such as 2014-01-03. For retaining other date
format in the CONCATENATE function, you can change the yyyy-mm-dd to the certain
date format code as you need.

This section is talking about keeping number format, such as currency format, percentage
format, decimal places of numbers, etc. in the CONCATENATE function in Excel.

1. First of all, we need to get the format code of specified numbers you will concatenate:
right click the number cell and select the Format Cells from the right-clicking menu. See
screenshot:

2. In the opening Format Cells dialog box, go to the Number tab, click the Custom in
the Category box, copy the format code in the Type box, and close the Format Cells
dialog box. See screenshot:
 3. Then we will convert the number
you will concatenate into text string.
Select a blank cell (Cell E2 in our
example), enter the formula

=TEXT(B2,"$#,##0.00_);($#,##0.00)") (B2 is the currency cell you will concatenate)

into it, and press the Enter key.

Notes:
(1) In above format, $#,##0.00_);($#,##0.00) is the format code we copied in step 2. And
you can change the format code to your copied one.
(2) Sometimes the format code of currency you copied may be very complicated which
causes formula errors. If so, please simply the format code such as
$#,##0.00_);($#,##0.00).

4. If there are several kinds of numbers you will concatenate, please repeat Step 1-3 to
convert these numbers to text strings.
5. After converting numbers with special number formats to text string, we can
concentrate them by entering formula =CONCATENATE(A2, " ",E2, " ", F2) (A2, E2,
and F2 are cells we will concatenate) into a blank cell (Cell G1 in our example), and
press the Enter key. See screenshot:
6. Select the range E2:G2 and then drag the AutoFill Handle to the range as you need.
And then you will see each row has been concatenated with keeping all kinds of number
formats. See screenshot

Note: If you are familiar with the format codes of numbers you will concatenate, you can
apply the formula =CONCATENATE(A2, " ",TEXT(B2,"$#,##0.00_);($#,##0.00)"), " ",
TEXT(C2,"0.00%" )) and replace the format codes of $#,##0.00_);($#,##0.00) and 0.00%
based on your requirement.

Overview of Excel tables

To make managing and analyzing a group of related data easier, you can turn a range of
cells into an Excel table (previously known as an Excel list).

Notes:
 Excel tables should not be confused with the data tables that are part of a suite of
what-if analysis commands. For more information about data tables, see Calculate
multiple results with a data table.
 You cannot create or insert tables in a shared workbook.

A table can include the following elements:

Header row By default, a table has a header row. Every table column has
filtering enabled in the header row so that you can filter or sort your table data
quickly. For more information, see Filter data or Sort data.

You can turn off the

header row in a table.
For more
information, see
Turn Excel table headers on or off.

Banded rows Alternate shading or banding in rows helps to better distinguish the data.

Calculated columns By entering a formula in one cell in a table column, you can
create a calculated column in which that formula is instantly applied to all other cells in
that table column. For more information, see Use calculated columns in an Excel table.
Total row Once you add a total row to a table, Excel gives you an AutoSum drop-down
list to select from functions such as SUM, AVERAGE, and so on. When you select one
of these options, the table will automatically convert them to a SUBTOTAL function,
which will ignore rows that have been hidden with a filter by default. If you want to
include hidden rows in your calculations, you can change the SUBTOTAL function
arguments.

For more information, also see Total the data in an Excel table.

Sizing handle A sizing handle

in the lower- right corner of the table
allows you to drag the table to the size
that you want.
For other ways to resize a table, see Resize a table by adding rows and columns.

Create a table

You can create as many tables as you want in a spreadsheet.

To quickly create a table in Excel, do the following:

1. Select the cell or the range in the data.

2. Select Home > Format as Table.
3. Pick a table style.
4. In the Format as Table dialog box, select the checkbox next to My table as headers
if you want the first row of the range to be the header row, and then click OK.

Working efficiently with your table data

Excel has some features that enable you to work efficiently with your table data:

 Using structured references Instead of using cell references, such as A1 and

R1C1, you can use structured references that reference table names in a formula. For
more information, see Using structured references with Excel tables.
 Ensuring data integrity You can use the built-in data validation feature in Excel.
For example, you may choose to allow only numbers or dates in a column of a table.
For more information on how to ensure data integrity, see Apply data validation to
cells.

Export an Excel table to a SharePoint site

If you have authoring access to a SharePoint site, you can use it to export an Excel table
to a SharePoint list. This way other people can view, edit, and update the table data in the
SharePoint list. You can create a one-way connection to the SharePoint list so that you
can refresh the table data on the worksheet to incorporate changes that are made to the
data in the SharePoint list. For more information, see Export an Excel table to
SharePoint.

Creating a Table within Excel

1. Open the Excel spreadsheet.

2. Use your mouse to select the cells that contain the information for the table.
3. Click the "Insert" tab > Locate the "Tables" group.
4. Click "Table". A "Create Table" dialog box will open.
5. If you have column headings, check the box "My table has headers".
6. Verify that the range is correct > Click [OK].
7. Resize your columns to make the headings visible.
Changing the Table Style

1. Click on a cell in the table to activate the "Table Tools" tab.

2. Click the "Design" tab > Locate the "Table Styles" group.
3. Choose a style/color option that appeals to you. (Hover over the various table
styles to see a live preview.)

Automatic Expansion

Check your Options

When you insert rows (or columns) at the end of a formula range the formulas (by
default) will be expanded automatically.
Formulas will only expand automatically when you insert rows (or columns) at the end of
a formula range when the following option is selected.
Check your (Options, Advanced tab)(Extend data range formats and formulas).

Extend Data Range Formats

If an AutoFormat has been applied to the list then the format is not automatically
extendedDates are not automatically formatted
Any data pasted from the clipboard will not have automatic formats applied to it.

Extend Formulas

Formulas will not be automatically expanded when you insert rows (or columns) at the
start of a formula range.
Formulas will not be automatically expanded (or copied) when you paste data from the
clipboard.

Extend Formulas - Copying - Typing at the End

Excel will automatically copy formulas when you add new data to a table if the same
formula appears in four or more consecutive rows or columns.
Typing May below the table and inserting numerical data into cells "C7", "D7" and "E7"
will cause the formula in column "F" to be automatically copied to cell "F7".
Extend Formulas - Inserting in the Middle

Formulas are always automatically expanded when you insert new rows and columns
inside existing formulas.
Inserting a row above row 5 (i.e. in the middle) will cause the SUM() formula to
automatically expand.

The same is true when you have formulas that refer to columns and you insert new
columns inside existing formulas.

In these situations the formulas will always expand (automatically) and there is no option
to turn this off.

Extend Formulas - Inserting - At The End

Excel will automatically expand formulas at the end of a formula range if they refer to
three or more consecutive rows or columns.
Inserting a row underneath March and entering a number into cell "C6" will cause the
SUM() formula in cell "C7" to automatically expand.
The SUM() formula will only expand automatically if you enter numerical data into
cell "C6".

Formulas will not be expanded automatically if you insert rows (or columns) at the start
of a formula range.

Use the following ruler table names:

Using structured references with Excel tables

 Use valid characters Always start a name with a letter, an underscore character (_),
or a backslash (\). Use letters, numbers, periods, and underscore characters for the
rest of the name. You can’t use "C", "c", "R", or "r" for the name, because they’re
already designated as a shortcut for selecting the column or row for the active cell
when you enter them in the Name or Go To box
 Don’t use cell references Names can’t be the same as a cell reference, such as
Z$100 or R1C1.
 Don’t use a space to separate words Spaces can’t be used in the name. You can
use the underscore character (_) and period (.) as word separators. For example,
DeptSales, Sales_Tax or First.Quarter.
 Use no more than 255 characters A table name can have up to 255 characters.
 Use unique table names Duplicate names aren’t allowed. Excel doesn’t distinguish
between upper and lowercase characters in names so if you enter “Sales” but already
have another name called “SALES" in the same workbook, you’ll be prompted to
choose a unique name.

 Use an object identifier If you plan on having a mix of tables, PivotTables and
charts, it's a good idea to prefix your names with the object type. For example:
tbl_Sales for a sales table, pt_Sales for a sales PivotTable, and chrt_Sales for a sales
 chart, or ptchrt_Sales for a sales PivotChart. This keeps all of your names in an
ordered list in the Name Manager.

Structured reference syntax rules

You can also enter or change structured references manually in the formula but to do that,
it will help to understand structured reference syntax. Let’s go over the following formula
example:

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

This formula has the following structured reference components:

 Table name: DeptSales is a custom table name. It references the table data,
without any header or total rows. You can use a default table name, such as Table1,
or change it to use a custom name.

 Column specifier: [Sales Amount] and [Commission Amount] are column

specifiers that use the names of the columns they represent. They reference the
column data, without any column header or total row. Always enclose specifiers in
brackets as shown.

 Item specifier: [#Totals] and [#Data] are special item specifiers that refer to
specific portions of the table, such as the total row.

 Table specifier: [[#Totals],[Sales Amount]] and [[#Data],[Commission

Amount]] are table specifiers that represent the outer portions of the structured
reference. Outer references follow the table name, and you enclose them in square
brackets

 Structured reference: (DeptSales[[#Totals],[Sales Amount]] and

DeptSales[[#Data],[Commission Amount]] are structured references, represented
by a string that begins with the table name and ends with the column specifier.

To create or edit structured references manually, use these syntax rules:

 Use brackets around specifiers All table, column, and special item specifiers
need to be enclosed in matching brackets ([ ]). A specifier that contains other
specifiers requires outer matching brackets to enclose the inner matching brackets of
the other specifiers. For example: =DeptSales[[Sales Person]:[Region]]
 All column headers are text strings But they don’t require quotes when they’re
used in a structured reference. Numbers or dates, such as 2014 or 1/1/2014, are also
considered text strings. You can’t use expressions with column headers. For
example, the expression DeptSalesFYSummary[[2014]:[2012]] won’t work.

Use brackets around column headers with special characters If there are special
characters, the entire column header needs to be enclosed in brackets, which means that
double brackets are required in a column specifier. For example:
=DeptSalesFYSummary[[Total $ Amount]]

Here’s the list of special characters that need extra brackets in the formula:
 Tab
 Line feed
 Carriage return
 Comma (,)
 Colon (:)
 Period (.)
 Left bracket ([)
 Right bracket (])
 Pound sign (#)
 Single quotation mark (')
 Double quotation mark (")
 Left brace ({)
 Right brace (})
 Dollar sign ($)
 Caret (^)
 Ampersand (&)
 Asterisk (*)
 Plus sign (+)
 Equal sign (=)
 Minus sign (-)
 Greater than symbol (>)
 Less than symbol (<)
 Division sign (/)
 Use an escape character for some special characters in column headers Some
characters have special meaning and require the use of a single quotation mark (')
as an escape character. For example: =DeptSalesFYSummary['#OfItems]

Here’s the list of special characters that need an escape character (‘) in the formula:

 Left bracket ([)

 Right bracket (])
 Pound sign(#)
 Single quotation mark (')

Use the space character to improve readability in a structured reference You can use
space characters to improve the readability of a structured reference. For example:
=DeptSales[ [Sales Person]:[Region] ] or =DeptSales[[#Headers], [#Data], [%
Commission]]

It’s recommended to use one space:

 After the first left bracket ([)

 Preceding the last right bracket (]).
 After a comma.

Strategies for working with structured references

Consider the following when you work with structured references.

 Use Formula AutoComplete You may find that using Formula AutoComplete is
very useful when you enter structured references and to ensure the use of correct
syntax. For more information, see Use Formula AutoComplete.
 Decide whether to generate structured references for tables in semi-
selections By default, when you create a formula, clicking a cell range within a
table semi-selects the cells and automatically enters a structured reference instead of
the cell range in the formula. This semi-selection behavior makes it much easier to
enter a structured reference. You can turn this behavior on or off by selecting or
clearing the Use table names in formulas check box in the File > Options >
Formulas > Working with formulas dialog.
 Use workbooks with external links to Excel tables in other workbooks If a
workbook contains an external link to an Excel table in another workbook, that
linked source workbook must be open in Excel to avoid #REF! errors in the
destination workbook that contains the links. If you open the destination workbook
first and #REF! errors appear, they will be resolved if you then open the source
workbook. If you open the source workbook first, you should see no error codes.
 Convert a range to a table and a table to a range When you convert a table to a
range, all cell references change to their equivalent absolute A1 style references.
When you convert a range to a table, Excel doesn’t automatically change any cell
references of this range to their equivalent structured references.
 Turn off column headers You can toggle table column headers on and off from
the table Design tab > Header Row. If you turn off table column headers, structured
references that use column names aren’t affected, and you can still use them in
formulas. Structured references that refer directly to the table headers (e.g.
=DeptSales[[#Headers],[%Commission]]) will result in #REF.
 Add or delete columns and rows to the table Because table data ranges often
change, cell references for structured references adjust automatically. For example,
if you use a table name in a formula to count all the data cells in a table, and you
then add a row of data, the cell reference automatically adjusts.
 Rename a table or column If you rename a column or table, Excel automatically
changes the use of that table and column header in all structured references that are
used in the workbook.
 Move, copy, and fill structured references All structured references remain the
same when you copy or move a formula that uses a structured reference.

Fill a formula down into adjacent cells

Follow these steps to fill a formula and choose which options to apply:

1. Select the cell that has the formula you want to fill into adjacent cells.

2. Drag the fill handle across the cells that you want to fill.
3. If you don’t see the fill handle, it might be hidden. To display it again:

a) Click File > Option.

b) Click Advanced.
c) Under Editing Options, check the Enable fill handle and cell drag-and-drop box.

 To change how you want to fill the selection, click the small Auto Fill Options icon
that appears after you finish dragging, and choose the option that want.

Fill formulas into adjacent cells

You can use the Fill command to fill a formula into an adjacent range of cells. Simply do
the following:
1. Select the cell with the formula and the adjacent cells you want to fill.
2. Click Home > Fill, and choose either Down, Right, Up, or Left.

 Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a
column, or Ctrl+R to fill the formula to the right in a row.

Turn workbook calculation on

Formulas won’t recalculate when you fill cells if automatic workbook calculation isn’t
enabled.

Here’s how you can enable it:

1. Click File > Options.

2. Click Formulas.
3. Under Workbook Calculation, choose Automatic.

Use AutoSum to sum numbers

If you need to sum a column or row of numbers, let Excel do the math for you. Select a
cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter,
and you’re done.

When you click AutoSum, Excel automatically enters a formula (that uses the SUM
function) to sum the numbers.

Here’s an example. To add the January numbers in this Entertainment budget, select cell
B7, the cell immediately below the column of numbers. Then click AutoSum. A formula
appears in cell B7, and Excel highlights the cells you’re totaling.
Press Enter to display the result (95.94) in cell B7. You can also see the formula in the
formula bar at the top of the Excel window.

Notes:To sum a column of numbers, select the cell immediately below the last number
in the column. To sum a row of numbers, select the cell immediately to the right.

AutoSum is in two locations: Home > AutoSum, and Formulas > AutoSum.

Once you create a formula, you can copy it to other cells instead of typing it over and
over. For example, if you copy the formula in cell B7 to cell C7, the formula in C7
automatically adjusts to the new location, and calculates the numbers in C3:C6.

You can also use AutoSum on more than one cell at a time. For example, you could
highlight both cell B7 and C7, click AutoSum, and total both columns at the same time.

You can also sum numbers by creating a simple formula.

column label replacement

The default method for including a column reference in an Excel formula is to use the
column letter, a convention that may make it difficult to interpret the parts of complex
formulas. Microsoft designed Excel with a method for naming cell ranges and columns
to simplify writing and interpreting formulas. You can apply column names to a single
worksheet or increase the scope and apply it to an entire workbook.

Single Sheet

1) Click the letter of the column you want to rename to highlight the entire column.
2) Click the "Name" box, located to the left of the formula bar, and press "Delete" to
remove the current name.
3) Enter a new name for the column and press "Enter."

Workbook

1. Click the letter of the column you want to change and then click the "Formulas" tab.
2. Click "Define Name" in the Defined Names group in the Ribbon to open the New
Name window.
3. Enter the new name of the column in the Name text box.
4.Click the "Scope" drop-down menu and select "Workbook" to apply the change to all
of the sheets in the workbook. Click "OK" to save your changes.

Other ribbon settings

You can customize the Ribbon by creating your own tabs with whichever commands
you want. Commands are always housed within a group, and you can create as many
groups as you want in order to keep your tab organized. If you want, you can even add
commands to any of the default tabs, as long as you create a custom group in the tab.

1. Right-click the Ribbon and select Customize the Ribbon... from the drop-down
menu.
2. The Excel Options dialog box will appear. Locate and select New Tab.

3. Make sure the New Group is selected, select a command, then click Add. You can
also drag commands directly into a group.
4. When you are done adding commands, click OK. The commands will be added to the
Ribbon.
  If
you
don't
see
the

command you want, click the Choose commands from: drop-down box and select
All Commands

Excel SUBTOTAL Function

Summary

The Excel SUBTOTAL function returns an aggregate result for supplied values.
SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table
below), and SUBTOTAL function can either include or exclude values in hidden rows.

Purpose

Get a subtotal in a list or database

Return value

A number representing a specific kind of subtotal

Syntax

=SUBTOTAL (function_num, ref1, [ref2], ...)

Arguments

 function_num - A number that specifies which function to use in calculating

subtotals within a list. See table below for full list.
 ref1 - A named range or reference to subtotal.
 ref2 - [optional] A named range or reference to subtotal.

Usage notes

Use the SUBTOTAL function to get a subtotal in a list or database. SUBTOTAL has the
ability to use a variety of functions when subtotaling, including AVERAGE, COUNT,
MAX, and others (see table below for a complete list). By default, SUBTOTAL excludes
values in rows hidden by a filter, as explained below. This makes SUBTOTAL very
useful in Excel Tables.

Below are a few examples of SUBTOTAL configured to SUM, COUNT, and

AVERAGE the values in a range. Notice the only difference is the value used for the
function_num argument:

=SUBTOTAL(109,range) // SUM
=SUBTOTAL(103,range) // COUNT
=SUBTOTAL(101,range) // AVERAGE

Available functions

SUBTOTAL behavior is controlled by the function_num argument, which is provided as

a numeric value. The table below shows available functions:
Function Include hiddenIgnore hidden
AVERAGE1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV 7 107
STDEVP 8 108
SUM 9 109
VAR 10 110
VARP 11 111

Manually hidden values

Notice the values used for the function_num argument are "paired" (e.g. 1-101, 2-102,
3-103, ...). This is related to how SUBTOTAL deals with manually hidden rows. When
function_num is between 1-11, SUBTOTAL includes cells that have been manually
hidden. When function_num is between 101-111, SUBTOTAL ignores values in rows
that have been manually are hidden.

SUBTOTAL always ignores values in cells are hidden with a filter. Values in "filtered
out" rows are not included, regardless of function_num.

Notes

 When function_num is between 1-11, SUBTOTAL includes values that are

hidden
 When function_num is between 101-111, SUBTOTAL excludes values that are
hidden
 In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of
function_num.
 SUBTOTAL ignores other subtotals that exist in references are ignored to
prevent double-counting
  SUBTOTAL is designed to work with vertical data values arranged vertically. In
Horizontal ranges, values in hidden columns are always included.

SUBTOTAL formula examples

Count visible rows in a filtered list

If you want to count the number of visible items in a filtered list, you can use the
SUBTOTAL function, which automatically ignores rows that are hidden by a filter.

The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN,
and more. (For a full list, see the table here). What makes SUBTOTAL especially
interesting and useful is that it automatically ignores items that are not visible in a
filtered list or table. This makes it ideal for showing how many items are visible in a list,
the subtotal of visible rows, etc.

Count visible rows only with criteria

To count visible rows only with criteria, you can use a rather complex formula based on
SUMPRODUCT, SUBTOTAL, and OFFSET. In the example shown, the formula in C12
is:=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-
MIN(ROW(C5:C8)),0))))
Sum visible rows in a filtered list

If you want to sum only the visible rows in a filtered list (i.e. only those rows not filtered
out), you can use the SUBTOTAL function with function number 9 or 109. What makes
SUBTOTAL especially useful is that it automatically ignores rows that are hidden in a
filtered list or table.

Using Nested Functions

A nested function uses a function as one of the arguments. Excel allows you to nest up to
64 levels of functions. Users typically create nested functions as part of a conditional
formula. For example, IF(AVERAGE(B2:B10)>100,SUM(C2:G10),0). The AVERAGE
and SUM functions are nested within the IF function. The structure of the IF function is
IF(condition_test, if_true, if_false). You can use the AND, OR, NOT, and IF functions to
create conditional formulas. When you create a nested formula, it can be difficult to
understand how Excel performs the calculations. You can use the Evaluate Formula
dialog box to help you evaluate parts of a nested formula one step at a time.

Create a Conditional Formula Using a Nested Function

1. Click the cell where you want to enter the function.

2. Click the Formulas tab.
3. Type = (an equal sign).
4. Click a button from the Function Library with the type of function you want to use,
click a submenu if necessary, and then click the function you want to insert into a

formula.

For example, click the Logical & Reference button, and then click COUNTIF.

Excel inserts the function you selected into the formula bar with a set of parenthesis, and
opens the Function Arguments dialog box.

Type a function as an argument to create a nested function, or a regular argument.

5. For example, =COUNTIF(E6:E19), “>”&AVERAGE(E6:E19)).

6. Click OK.
Conditional Formula Examples

Formula Result
=AND(A2>A3, A2<A4) If A2 is greater than A3 and less than A4, then return
TRUE, otherwise return FALSE
=OR(A2>A3, A2<A4) If A2 is greater than A3 or A2 is less than A4, then return
TRUE, otherwise return FALSE
=NOT(A2+A3=24) If A2 plus A3 is not equal to 24, then return TRUE,
otherwise return FALSE
IF(A2<>15, “OK”, “Not If the value in cell A2 is not equal to 15, then return “OK”,
OK”) otherwise return “Not OK”

Evaluate a Nested Formula One Step at a Time

1) Select the cell with the nested formula you want to evaluate. You can only evaluate
one cell at a time.
2) Click the Formulas tab.
3) Click the Evaluate Formula button.
4) Click Evaluate to examine the value of the underlined reference.The result of the
evaluation appears in italics.
5) If the underlined part of the formula is a reference to another formula, click Step In
to display the other formula in the Evaluation box.

The Step In button is not available for a reference the second time the reference appears
in the formula, or if the formula refers to a cell in a separate workbook.

6) Continue until each part of the formula has been evaluated, and then click Close.
7) To see the evaluation again, click Restart.

Some parts of formulas that use IF and CHOOSE functions are not evaluated, and #NA
is displayed. If a reference is blank, a zero value (0) is displayed.

Lookup & Reference Functions

Vlookup | Hlookup | Match | Index | Choose

Learn all about Excel's lookup & reference functions such as the VLOOKUP,
HLOOKUP, MATCH, INDEX and CHOOSE function.
Vlookup
The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a
table, and then returns a value in the same row from another column you specify.

1. Insert the VLOOKUP function shown below.

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of
the range $E$4:$G$7 and returns the value in the same row from the third column (third
argument is set to 3). The fourth argument is set to FALSE to return an exact match or a
#N/A error if not found.

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7)
stays the same, while the relative reference (A2) changes to A3, A4, A5, etc. Visit our
page about the VLOOKUP function for much more information and many examples.
Hlookup
In a similar way, you can use the HLOOKUP (Horizontal lookup) function.

Match
The MATCH function returns the position of a value in a given range.
Explanation: Yellow found at position 3 in the range E4:E7. The third argument is
optional. Set this argument to 0 to return the position of the value that is exactly equal to
lookup_value (A2) or a #N/A error if not found. Use INDEX and MATCH in Excel and
impress your boss.
Index
The INDEX function below returns a specific value in a two-dimensional range.

Explanation: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

The INDEX function below returns a specific value in a one-dimensional range.

Explanation: 97 found at position 3 in the range E4:E7. Use INDEX and MATCH in
Excel and impress your boss.
Choose
The CHOOSE function returns a value from a list of values, based on a position number.

Explanation: Boat found at position 3.

How to use CONCATENATE and SUMIF together

PREREQUISITE

Know how to use "CONCATENATE" and "SUMIF" individually. Please refer to

Microsoft help for
Syntax.

What is concatenation means, please read

here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.
Simple Example

Lets consider a simple scenario,

Data given (raw data) : quantity produced by different machines on different days

Data needed : mapping (matrix) qty produced by different machines on different days.
THE STEPS (HOW TO DO)

Stap 1: Create a new column to join date and machine using "Concatenate".

Note I am using "-" to make sure no duplicates.

the formula in column (refer to the image) is =CONCATENATE(N3,"-",O3).

Make sure the formula is dragged to all the way to the bottom of the table.

Step 2: create the matrix

Manufacturing date in the column and machines in the raw (it can be done vise-
verse also)
Step 3: formula to calculate the total quantity produced by each machines on different
days.
Use sumif to do the calculation. Please note the range column and sum_range column
are different. Lets start in cell B3.

range Required. Column M (where the concatenate values are)

criteria Required. CONCATENATE($A3,"-",B$2)
sum_range Required . Column P

Let's focus on CONCATENATE($A3,"-",B$2). This formula is the same as in column

M. The main difference in freezing column and raw values. $A3 is column freeze
and B$2 is raw freeze. The freezing allow us to drag the formula.

Step 4: Drag the formula to fill up the entire table

DONE

Step 5: Check and Balance

Always good to do a check on the accuracy.