SUM(*number1*, *number2*, ...)

The SUM function lets you add values in cell ranges, arrays, constants. You can have up to 255 different arguments.

- Add values in a cell range
- Add values in an array
- Multiple cell ranges
- Numbers and text
- Boolean values
- Expanding cell reference
- SUM with a condition
- SUM with multiple conditions
- SUM - VBA example
- Download excel *.xlsm file
- SUM formula examples

The SUM function lets you add values in a cell range, like this = SUM(B3:B7), instead of adding values in a formula using the plus sign, like this =B3+B4+B5+B6+B7.

The SUM function lets you type one or multiple cell ranges, in this example only cell range B3:B7 is entered as an argument. See above picture.

An array is multiple values enclosed with a beginning and ending curly bracket, you can easily convert a cell range to an array. See instructions below.

Select a cell and type =SUM(B3:B9)

Click in the formula bar and select B3:B9.

Press F9 and the cell range is converted to an array, like this: =SUM({5,3,6,4,2})

Press Enter.

The SUM function adds the values in the array 5+3+6+4+2 = 20. When you convert a cell range to values you hard-code or create constants in your formula, meaning they never change unless you change the values in the formula.

Cell references on the other hand change if you change the values on a worksheet.

I recommend reading this post: Learn the basics of Excel arrays , if you want to learn more about array formulas.

If you want to add values in multiple cell ranges you simply use a comma between arguments. Check your regional settings if a comma doesn't work for you. You are allowed to have up to 255 arguments in one SUM function.

The formula in cell B8 adds the values in cell range B3:B7. 5 + AA + 6 + 4 +2 = 17. The SUM function ignores text strings, in this case AA.

Cell range B3:B7 contains boolean values, TRUE or FALSE, however SUM can't add boolean values. Don't worry, there is a work-around.

There are multiple solutions to this problem, here are a few:

=SUM(--(B3:B7))

=SUM(B3:B7+0)

=SUM(B3:B7*1)

They need to be entered as array formula, because they do calculations to a cell range containing multiple cells. Instructions below on how to enter an array formula.

- Double click cell B8
- Type =SUM(B3:B7*1)
- Press CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys

The formula in the formula bar changes to {=SUM(B3:B7*1)}

These curly brackets tells you that you have created an array formula, don't enter these characters yourself.

The formula returns 2 because TRUE equals 1 and FALSE equals 0. 1+0+1+0+0 = 2.

The following picture shows you numbers in column B.

Enter this formula in cell C3:

=SUM($B$3:B3)

Make sure you get the dollar signs right, they are important. The cell reference changes as you copy the formula and paste it to cells below.

Select cell C4 and see how the formula changed in the formula bar. The part of the cell reference without dollar signs changed from B3 to B4.

That part is a relative cell reference and the part with dollar signs is an absolute cell reference.

Read more here: Absolute and relative cell references

The picture below shows you two columns. Column B contains text values and column C contains numbers.

The formula in cell F3 lets you add numbers in column C if their adjacent value is equal to the value in cell F2:

=SUM((B3:B10=F2)*C3:C10)

This formula is an array formula, read Boolean values above how to enter an array formula.

The equal sign in B3:B10=F2 lets you compare the values in cell range B3:B10 with the value in cell F2. The equal sign is a logical operator, often used in IF functions.

This logical test returns an array of boolean values, in this case {FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE} The parentheses (B3:B10=F2) makes sure this part of the formula is calculated first before multiplying with the numbers in cell range C3:C10.

(B3:B10=F2)*C3:C10 becomes {FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE}*C3:C10 and returns {0; 1; 10; 0; 8; 0; 0; 0}

The SUM function then adds the number in the array: SUM({0; 1; 10; 0; 8; 0; 0; 0}) and returns 19 in cell F3. 1+10+8 = 19

**Tip!** The powerful excel table can do all this for you:

Excel defined table - SUM with criteria

Adding a second condition to the formula is easy. Simply add your condition to the formula enclosed with parentheses.

=SUM((B3:B10=G2)*(C3:C10=G3)*D3:D10)

Sub HLP() MsgBox Application.WorksheetFunction.Sum(Range("B3:B7")) End Sub

- Overview
- Arguments
- Approximate match
- Horizontal and vertical lookup
- Horizontal and vertical lookup - INDEX + MATCH
- HLOOKUP - multiple tables
- VBA Example
- Download excel *.xlsm file
- Functions in this post

The HLOOKUP function lets you search the top row in a data range for a value and return another value on the same column in a row you specify.

HLOOKUP stands for horizontal lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look horizontally in the top row of the data array. VLOOKUP stands for vertical lookup but that is another post.

The HLOOKUP function in cell E2 searches the top row of cell range B5:E14 for value "Item" and it is found in the second column. The third argument tells the function which row to fetch the value from, in this case it is four.

The intersection of the second column and the fourth row is value DD and that is the returning value in cell E2.

Formula in cell E2:

=HLOOKUP(C2,B5:E14,C3,FALSE)

*Lookup_value* - The first argument is the lookup value. This is the value you want to find in the top row of the table array.

*Table_array *- This is a cell range or an array that you want to search.

*Row_index_num *- Here you specify which row you want fetch the value from.

*Range_lookup *- You may select TRUE or FALSE here, TRUE tells the function to do an approximate match and this is the default value.

Make sure the top row in the table array is sorted ascending. FALSE means exact match and this mode is probably what you will use the most. Take note that if you use the wrong argument here you may get incorrect results.

The HLOOKUP function allows you to search with an approximate match if you enter TRUE in the fourth argument. In the example below there are four different ranges, 0 and less than 100, 100 and less than 250, 250 and less than 500 and 500 and more.

The value 189 in cell C2 can't be found in the top row of C5:F6 so the next largest value that is less than lookup_value (189) is matched, in this case 100. The corresponding value is 12.1 and is returned in cell C3.

The following formula lets you search both horizontally and vertically. The formula in cell E2 looks for Item in the top horizontal row and 141 in column "Invoice", the intersecting cell is C9 so the formula returns AA.

Formula in cell E2:

=HLOOKUP(C2,B5:E14,MATCH(C3,B5:B14,0),FALSE)

You can also use INDEX and MATCH functions to get a value in a cross reference table. The formula below works just like the formula above.

Formula in cell E2:

=INDEX(B6:E14,C3,MATCH(C2,B5:E5,0))

This formula is actually easier to understand and remember than the HLOOKUP function, this is the method I prefer.

The formula below lets you also choose a table to search. The INDIRECT function lets you convert a cell value to a cell reference, however the INDIRECT function is volatile, so use it with care.

Volatile means it is recalculated every time the worksheet is recalculated. If you have many INDIRECT formulas, they may slow down your workbook.

Formula in cell J4:

=HLOOKUP(H4,INDIRECT(G4),I4,FALSE)

The following macro lets you search a table using the parameters in cell C2 and C3. A messagebox appears and tells you the returning value.

**VBA Code**

Sub HLP() MsgBox Application.WorksheetFunction.HLookup(Range("C2"), Range("B5:E14"), Range("C3"), False) End Sub

INDEX(array,row_num,[column_num])

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value, lookup_array, [match_type])

Returns the relative position of an item in an array that matches a specified value in a specific order

IFERROR(value, value_if_error)

Returns value_if_error if expression is an error and the value of expression itself otherwise.

- Overview
- Arguments
- Approximate match
- Related tables
- Dynamic arguments
- INDEX + MATCH
- VLOOKUP error
- VBA Example
- Using multiple conditions in VLOOKUP
- Download excel *.xlsm file

The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.

VLOOKUP stands for vertical lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look vertically in the data array. HLOOKUP stands for horizontal lookup but that is for another post.

**Formula in cell C3:**

=VLOOKUP(B3,B6:E18,4,FALSE)

The VLOOKUP function uses lookup value AA-1611 in cell B3 and searches Table1 (cell range B6:E18) in the leftmost column. A matching value is found on row 16.

The third argument is which column you want to fetch the corresponding value from. In this example column 4 is entered and £30.00 is returned in cell C3.

The fourth and last argument lets you choose between approximate and exact match, in this case FALSE meaning EXACT match.

*lookup_value - *Value you want to look up

*table_array - *The range you want to use, remember that the VLOOKUP function always looks in the leftmost column in your specified range.

*col_index_num - *The column number which contains the return value

*[range_lookup]* - True or False (boolean value). True - approximate match, leftmost column must be sorted ascending. False - Exact match

The fourth argument allows you to choose if you want APPROXIMATE or EXACT match. You want, in most cases, to use the EXACT match however the default value is APPROXIMATE match. So make sure you know what you are doing.

This example below shows you the difference between EXACT and APPROXIMATE match.

You must have your leftmost column sorted in an ascending order or you may get incorrect results.

Approximate match is useful if you want to find which group or range your value belongs to. The example above shows that value 158 is between 100 and 200 so the return value is 15.

**Formula in cell C3:**

=VLOOKUP(B3,B6:C12,2,TRUE)

Read more: Use VLOOKUP to calculate discount percentages

A pivot table can't work with related tables (power pivots do) however the VLOOKUP function can quickly merge related tables so you then can analyze data in a pivot table.

These two tables share a data column (invoice), you can use the following formula to merge these two tables.

**Formula in cell E3:**

=VLOOKUP($B3, $B$18:$D$30, COLUMN(B2),FALSE)

Copy formula in cell E3 and paste to E3:F15.

This example shows you how to search multiple tables. The image below shows two excel tables, table10 and table20.

**Formula in cell E3:**

=VLOOKUP(B3,INDIRECT(C3),D3,FALSE)

The INDIRECT function lets you use a cell value as the *table_array *argument, allowing you to search multiple tables.

The VLOOKUP function is that it can only look for a value in the leftmost column. If you don't have your values in the leftmost column you have two options, rearrange your table or use another method, shown below.

The INDEX and MATCH function allows you to search any column in a table and return a value in any column on the same row. This formula is so versatile that I actually prefer INDEX + MATCH over the VLOOKUP function.

**Formula in cell C3:**

=INDEX(G6:G18,MATCH(D3,E6:E18,0))

The MATCH function lets you look for a value in a column and return the relative position of the found value in the array. AA-1611 is found on row 11 in cell range C6:C18

Read more: How to return multiple values using vlookup

The IFERROR function allows you to return a value if the VLOOKUP function returns an error, in this case NOT FOUND!

=IFERROR(VLOOKUP(B3,Table116,4,FALSE),"NOT FOUND!")

The following VBA code demonstrates how to use VLOOKUP in a macro.

The macro searches B6:B18 using the the value in cell C2 and displays the result in a messagebox.

VBA code Sub VLP() MsgBox Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:E18"), 4, False) End Sub

Hello Mr Oscar

I have the matter to create a megaformula to categorize my list. For short example:A1: Cash in deposit (Branch A t/t)

A2: Borrowed from Corp. A

A3: Interest payment

A4: Int.panalty pmt

A5: Prin. Pmt

A6: Salary Pmt on April

A7: Sales abroad

A8: Branch C t/t

A9: Transferred from Company AA

A10: Mortgages to DD ltd

A11: Sal. Pmt on Mayand

at B1 cell, I create a formula as follows:=IF(COUNT(SEARCH({"branch","corp.", "company"},A1))>0,"Precol.", IF(COUNT(SEARCH({"interest","int.", "prin."},A1))>0,"lo.",IF(COUNT(SEARCH("sales", A1))>0,"Sa.",IF(COUNT(SEARCH({"sal.","Salary", "wage","payroll"},A1))>0,"Se.","Others"))))But, my formula is too long and too many parentheses. I want to shorten this formula or replace by another. But how? Could you please to solve my question?

I have the matter to create a megaformula to categorize my list. For short example:A1: Cash in deposit (Branch A t/t)

A2: Borrowed from Corp. A

A3: Interest payment

A4: Int.panalty pmt

A5: Prin. Pmt

A6: Salary Pmt on April

A7: Sales abroad

A8: Branch C t/t

A9: Transferred from Company AA

A10: Mortgages to DD ltd

A11: Sal. Pmt on Mayand

at B1 cell, I create a formula as follows:=IF(COUNT(SEARCH({"branch","corp.", "company"},A1))>0,"Precol.", IF(COUNT(SEARCH({"interest","int.", "prin."},A1))>0,"lo.",IF(COUNT(SEARCH("sales", A1))>0,"Sa.",IF(COUNT(SEARCH({"sal.","Salary", "wage","payroll"},A1))>0,"Se.","Others"))))But, my formula is too long and too many parentheses. I want to shorten this formula or replace by another. But how? Could you please to solve my question?

Thank you very much.Hung

By examining your formula it seems that you have different levels with search words. The word found with the lowest level should be returned, leave the remaining.

Example 1, cell A2 contains "test branch wage". "branch" is found on level 1 (cell E3) and "wage" on level 4 (cell F6). However "branch" is on the lowest level of the two so Precol. (cell H3) is returned in cell B2.

Example 2, cell A3 contains "abc sales payroll". "sales" is a search string found on level 3 and "payroll" is on level 4. Level 3 is the lowest level so "Sa." (cell H5) is returned in cell B3.

Example 3, cell A4 contains"anything whatever" and no search value is found except the asterisk (*) on level 5. Text string "Others" (cell H7) is returned in cell B4.

**Array formula in cell B2:**

=INDEX($H$3:$H$7, MIN(IF(ISNUMBER(SEARCH($E$3:$G$7, A2)), MATCH(ROW($E$3:$G$7), ROW($E$3:$G$7)), "")))

If you did above steps correctly excel automatically adds a beginning and ending curly bracket {*array_formula*} to the formula. Don't enter these characters yourself.

You can easily follow along as I explain this array formula. Select cell B2 and go to "Formulas" on the ribbon, click "Evaluate Formula" button. Click on "Evaluate" to go to next step.

**Step 1 - Search for multiple text strings simultaneously**

The SEARCH function returns the number of the character at which a specific character or text string is found. However, we are only interested if the string is found or not, this function is exactly what we need.

We are doing something strange with the SEARCH function below, we are not only using one string but multiple strings at once. That is why you see a cell range in the first argument.

SEARCH($E$3:$G$7,A2)

becomes

SEARCH({"branch","corp.","company";"interest","int.","prin.";"sales","-","-";"Salary","wage","payroll";"*","-","-"},"test branch wage")

and returns

{6, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!;#VALUE!, #VALUE!,#VALUE!; #VALUE!,13,#VALUE!; 1,#VALUE!, #VALUE!}

The SEARCH function returns an array with the same size as the cell range used in the first argument, see above.

$E$3:$G$7 is a cell range containing multiple columns and rows. An array with multiple columns and rows uses commas and semicolons as delimiting characters.

A comma is used as a delimiting character to separate values column by column and a semi colon is used to separate values row by row.

$E$3:$G$7 has three columns, E, F and G. The returning array above contains three values separated by two commas and then a semicolon, so the array has also three columns. The same with the number of rows in cell range $E$3:$G$7 and rows in array.

**Step 2 - Look for numbers in array**

A number indicates that the text string is found, an error #VALUE tells you that no search string is found. The ISNUMBER function converts numbers to TRUE and all other values including errors to FALSE.

ISNUMBER(SEARCH($E$3:$G$7,A2))

becomes

ISNUMBER({6,#VALUE!,#VALUE!;#VALUE!, #VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!; #VALUE!,13,#VALUE!;1,#VALUE!,#VALUE!})

and returns {TRUE, FALSE, FALSE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE;TRUE, FALSE, FALSE}

**Step 3 - If text string is found return corresponding row number**

IF(ISNUMBER(SEARCH($E$3:$G$7, A2)), MATCH(ROW($E$3:$G$7), ROW($E$3:$G$7)),"")

becomes

IF({TRUE, FALSE, FALSE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE;TRUE, FALSE, FALSE}, MATCH(ROW($E$3:$G$7), ROW($E$3:$G$7)), "")

becomes

IF({TRUE, FALSE, FALSE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE;TRUE, FALSE, FALSE}, {1; 2; 3; 4; 5}, "")

and returns

{1,"","";"","","";"","","";"",4,"";5,"",""}

**Step 4 - Extract smallest row number**

MIN(IF(ISNUMBER(SEARCH($E$3:$G$7, A2)), MATCH(ROW($E$3:$G$7), ROW($E$3:$G$7)), ""))

becomes

MIN({1,"","";"","","";"","","";"",4,"";5,"",""})

and returns 1.

**Step 5 - Return value using row number**

INDEX($H$3:$H$7, MIN(IF(ISNUMBER(SEARCH($E$3:$G$7, A2)), MATCH(ROW($E$3:$G$7), ROW($E$3:$G$7)), "")))

becomes

INDEX($H$3:$H$7, 1)

becomes

INDEX({"Precol.";"lo.";"Sa.";"Se.";"Others"}, 1)

and returns Precol. in cell B2.

**SEARCH(***find_text*,*within_text*, [*start_num*])

Returns the number of the character at which a specific character or text string is found reading left to rigt (not case-sensitive)

**IF**(*logical_test*, [*value_if_true*], [*value_if_false*])

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**ROW(**reference**)**

Returns the rownumber of a reference

I am working in a railway project as planner. How can i create a dynamic strip chart in excel?

Assume total length be 10 kilometers and each cell of 100 meters.

If I update the progress from 9.01 kms to 9.08 kms in the table, how the cell gets highlighted corresponding to the entered chainages.

Assume total length be 10 kilometers and each cell of 100 meters.

If I update the progress from 9.01 kms to 9.08 kms in the table, how the cell gets highlighted corresponding to the entered chainages.

**Answer:**

The worksheet above shows you km in column A and highlighted cells in column B, if they are complete in the excel defined table.

You can easily add or remove values to the excel defined table without the need to adjust the conditional formatting formulas.

**Conditional formatting formula applied to cell range B1:B101:**

=SUMPRODUCT((A1<INDIRECT("Table1[End]"))*(A1>=INDIRECT("Table1[Start]")))

- Select cell range D3:E6
- Go to tab "Insert" on the ribbon
- Click "Table" button

Tip! You can also use shortcut key CTRL + T to create a table

Excel tables lets you easily organize, filter and format data on a worksheet.

Learn more about excel tables.

- Select cell range B1:B101
- Go to tab "Home" on the ribbon if you are not already there
- Click on "Conditional Formatting" button to expand a menu
- Click "New Rule.."

- Click "Use a formula to determine which cells to format"
- Type above formula in dialog box formula bar
- Click "Format..." button
- Go to tab "Fill"
- Pick a fill color
- Click OK button
- Click OK button

You can't use structured references in a conditional formatting formula unless you use the INDIRECT function for each reference. This applies to Data Validation Lists as well.

**Step 1 - Check if current cell value is less than the END range values in table**

(A1<INDIRECT("Table1[End]")

becomes

0<{0.8;1.3;1.6}

and returns {TRUE;TRUE;TRUE}

**Step 2 - Check if current cell value is greater than or equal to the START values in table**

A1>=INDIRECT("Table1[Start]"

becomes

0>={0.1;1.1;1.5}

and returns {FALSE;FALSE;FALSE}

**Step 3 - Multiply arrays**

(A1<INDIRECT("Table1[End]"))*(A1>=INDIRECT("Table1[Start]"))

becomes

{TRUE;TRUE;TRUE}*{FALSE;FALSE;FALSE}

and returns {0;0;0}

Multiplying boolean values creates integers.

**Step 4 - Sum array**

SUMPRODUCT((A1<INDIRECT("Table1[End]"))*(A1>=INDIRECT("Table1[Start]")))

becomes

SUMPRODUCT( {0;0;0})

and returns 0.

0 means False so cell B1 is not formatted gray.

You can also use the methods described in this article to calculate commissions, tariffs, charges, shipping costs, packaging expenses etc.

- Use price ranges to determine discount
- Linear discounts
- Multiple tables
- Two-way lookup in multiple cross reference tables
- Download xlsx file
- Functions in this post

For this example our price range plan looks like this:

- Total price between 0 and 100 returns a 5% discount
- Total price between 100 and 300 returns a 7% discount
- Total price between 300 and 500 returns a 8% discount
- Total price between 500 and 1000 returns a 10% discount
- Total price between 1000 and above returns a 15% discount

The picture below shows you the discount percentage table (B2:D7) and a summary table (B9:E19) with a calculated discount (E21).

The table above shows you the total amount of all items $1 332.60 in cell E20, the formula in cell E21 calculates a discount percentage. Since $1 332.60 is larger than $1 000 the formula returns 15%.

Cell E23 returns the discounted total.

Keep in mind, the values in the first column (B3:B7) must be sorted ascending. The third column (D3:D7) is not necessary for the formula calculation.

**Formula in cell E21:**

=VLOOKUP(E20,B3:C7,2,TRUE)

**Formula in cell E22:**

=E20*E21

**Formula in cell E23:**

=E20-E22

The following chart shows you the different discount ranges from table above.

Read on to learn more about linear discounts.

In this scenario we want to use a linear equation to calculate the discount percentage. It begins on 0 (zero) with 2% and climbs up to 15% on $2 000, shown in cell range B3:C4 on the picture below.

These four numbers are our two coordinates. A coordinate in a plane has two values, x and y. You can draw a line between two coordinates thus a linear equation.

**Formula in cell E18:**

=(C4-C3)/(B4-B3)*E17+(C3-((C4-C3)/(B4-B3))*B3)

You can verify the calculated discount value by looking at this chart.

A linear equation looks like this: y=kx+m where k and m are constants. k is the slope of the line and m is the y-coordinate where the line crosses the y-axis.

A linear equation consists of two coordinates, in this case (0,0.02) and (2000,0.15).

k is (y2-y1)/(x2-x1) or using cell refs (C4-C3)/(B4-B3), x is cell E17.

m equals y if x is zero or using cell refs C3-((C4-C3)/(B4-B3))*B3

y = kx + m becomes (C4-C3)/(B4-B3)*E17+C3-((C4-C3)/(B4-B3))*B3

This example demonstrates how to use two different tables. The discount percentage depends on the category, the first table is category A and the second table is category B.

**Formula in cell G11:**

=VLOOKUP(E11,INDEX(($B$4:$C$7,$E$4:$F$7),,,MATCH(F11,{"A","B"},0)),2,TRUE)

You can easily follow the formula calculations, step by step.

- Select cell G11
- Go to tab "Formulas" on the ribbon
- Click "Evaluate formula" button

Click on "Evaluate" button to see next step in calculation, click OK when done.

**Step 1 - Identify relative position of category in array**

MATCH(F11,{"A","B"},0)

becomes

MATCH("B",{"A","B"},0)

and returns 2. B is the second number in the array {"A","B"}. In other words, the MATCH function converts the category to a number. The number determines which range to be used.

**Step 2 - Choose cell range**

INDEX(($B$4:$C$7,$E$4:$F$7),,,MATCH(F11,{"A","B"},0))

becomes

INDEX(($B$4:$C$7,$E$4:$F$7),,,2)

and returns $E$4:$F$7. The INDEX function returns cell range $B$4:$C$7 or $E$4:$F$7 depending on the category. An entire range is returned because both row and column parameters are omitted.

**Step 3 - Use cell range in VLOOKUP function and return corresponding discount percentage **

VLOOKUP(E11,INDEX(($B$4:$C$7,$E$4:$F$7),,,MATCH(F11,{"A","B"},0)),2,TRUE)

becomes

VLOOKUP(E11,$E$4:$F$7,2,TRUE)

becomes

VLOOKUP(1200,$E$4:$F$7,2,TRUE)

and returns 7%. The VLOOKUP function uses the selected range to retrieve the discount value from column 2 in the second table. Make sure the leftmost column in the tables are sorted descending.

Tip! This post explains how to use multiple conditions in a VLOOKUP function:

Use multiple conditions in Vlookup

This example shows you how to do lookups in multiple cross reference tables.

**Formula in cell S3:**

=INDEX(INDEX(($C$4:$G$8,$J$4:$N$8),,,MATCH(R3,{"North","South"},0)),MATCH(Q3,INDEX(($B$4:$B$8,$I$4:$I$8),,,MATCH(R3,{"North","South"},0)),0),MATCH(P3,INDEX(($C$3:$G$3,$J$3:$N$3),,,MATCH(R3,{"North","South"},0)),0))

The INDEX function allows you to choose between different cell ranges depending on the region value in cell R3, if a value is present in the optional parameter area_num.

You can easily follow the formula calculations, step by step.

- Select cell S3
- Go to tab "Formulas" on the ribbon
- Click "Evaluate formula" button

Click on "Evaluate" button to see next step in calculation, click OK when done.

**Step 1 - Identify which cell range to fetch data from, C4:G8 or J4:N8**

The MATCH function lets you find out where in an array a value is located (relative position).

INDEX(($C$4:$G$8,$J$4:$N$8),,,MATCH(R3,{"North","South"},0))

becomes

INDEX(($C$4:$G$8,$J$4:$N$8),,,1,0))

and returns the first cell reference $C$4:$G$8.

**Step 2 - Identify which cell range to search in, B4:B8 or I4:I8**

INDEX(($B$4:$B$8,$I$4:$I$8),,,MATCH(R3,{"North","South"},0))

becomes

INDEX(($B$4:$B$8,$I$4:$I$8),,,1)

and returns $B$4:$B$8

**Step 3 - Match value "D" in cell range $B$4:$B$8**

MATCH(Q3,INDEX(($B$4:$B$8,$I$4:$I$8),,,MATCH(R3,{"North","South"},0)),0)

becomes

MATCH(Q3,$B$4:$B$8,0)

becomes

MATCH("D",$B$4:$B$8,0)

and returns 4. Now we now the row number to use.

**Step 4 - Find column number**

The steps to look for the column number is almost identical to finding the row number.

MATCH(P3,INDEX(($C$3:$G$3,$J$3:$N$3),,,MATCH(R3,{"North","South"},0)),0)

becomes

MATCH(P3,INDEX(($C$3:$G$3,$J$3:$N$3),,,1),0)

becomes

MATCH(P3,$C$3:$G$3,0)

becomes

MATCH("G",$C$3:$G$3,0)

and returns 2.

**Step 5 - Return value from intersection of a row and column number**

INDEX(INDEX(($C$4:$G$8,$J$4:$N$8),,,MATCH(R3,{"North","South"},0)),MATCH(Q3,INDEX(($B$4:$B$8,$I$4:$I$8),,,MATCH(R3,{"North","South"},0)),0),MATCH(P3,INDEX(($C$3:$G$3,$J$3:$N$3),,,MATCH(R3,{"North","South"},0)),0))

becomes

INDEX($C$4:$G$8,4,2)

and returns 63.

You can easily modify the formula to search simultaneously in 4 different cross reference tables.

Example:

**Formula in cell S3:**

=INDEX(INDEX(($C$4:$G$8, $J$4:$N$8, $C$12:$G$16, $J$12:$N$16), , , MATCH(R3, {"North", "South", "West", "East"}, 0)), MATCH(Q3, INDEX(($B$4:$B$8, $I$4:$I$8, $B$12:$B$16, $I$12:$I$16), , , MATCH(R3, {"North", "South", "West", "East"}, 0)), 0), MATCH(P3, INDEX(($C$3:$G$3, $J$3:$N$3, $C$11:$G$11, $J$11:$N$11), , , MATCH(R3, {"North", "South", "West", "East"}, 0)), 0))

Tip! Read this post to do Reverse two-way lookup in a cross reference table.

VLOOKUP discount percentagesv2.xlsx

**VLOOKUP(**lookup_value, table_array, col_index_num,[range_lookup]**)**

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in an ascending order.

**INDEX(**array,row_num,[column_num],[area_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

Imagine you want to extract phone numbers from a text file or a worksheet, however the issue here is that there is also text combined with phone numbers and that makes it hard to filter out. Here a regular expression is coming to rescue.

Looking for 555-412395 in a cell range is easy in excel but looking for phone numbers with 3 digits then a hyphen and then 6 more digits is a lot harder. Each digit can be anything between 0 and 9.

You will find a guide later in this post to most of the characters you can use in regular expressions and how to solve the problem with phone numbers.

Nothing, you don't need to reference anything in VB Editor \ Tools for this to work. I will now demonstrate a simple UDF that counts matching strings in a cell range.

Function CountStringsCellRange(c As Range, pttrn As String) Rng = c.Value regexpattern = pttrn With CreateObject("vbscript.regexp") .Global = True .MultiLine = True .IgnoreCase = False .Pattern = regexpattern If c.Cells.CountLarge > 1 Then For rr = LBound(Rng, 1) To UBound(Rng, 1) For cc = LBound(Rng, 2) To UBound(Rng, 2) Set Results = .Execute(Rng(rr, cc)) i = i + Results.Count Next cc Next rr CountStringsCellRange = i Else Set Results = .Execute(Rng) CountStringsCellRange = Results.Count End If End With End Function

- Copy UDF above
- Start/Open Excel
- Go to VB Editor (Alt+F11)
- Click "Insert" on the menu at the top
- Click "Module"
- Paste code to code module

I will use the UDF above to show you how to work with patterns using specific characters. It is important to know which characters you can use and what they do.

**Search is case sensitive **

AbC - matches this exact string. A upper case letter A, a smaller case letter b and a upper case letter C

**A range of letters**

A hyphen allows you to search for a range of characters or numbers. [] brackets allow you to search for exactly one character.

[A-Z] finds a single upper case letter between A to Z.

[A-Z][a-z] matches a single uppercase letter between A to Z and then a lower case letter between a to z.

**Match at least one or more characters**

A+ matches a single A and multiple A's.

A+ matches A and AA and AAA but not B, three matches in total displayed in cell C9 in picture above.

**Match 0 or one character before it**

A? matches an empty string or one A.

**Match 0 or more characters**

A* matches an empty string or one or more A.

**Match any character except new line**

. matches any character

A.A matches AbA and ACA

**Multiple matches**

Curly brackets allows you to match multiple characters.

[A-Z]{4} is the same as [A-Z][A-Z][A-Z][A-Z] but easier to write. [A-Z]{4} matches 4 upper case letters between A-Z.

ABCD is the only match in string ABCD abcd aBcD to pattern [A-Z]{4}.

**NOT operator**

^ allows you to exclude certain characters

[^A] matches B, C and D because they are not equal to A.

**OR operator**

| is an OR operator.

A|C matches A and C in string ABCD.

**Escape characters**

\ (backslash) escapes characters.

If you are looking for question marks in a string the backslash allows you to escape certain characters that would otherwise have a different functionality.

**Match start and end of string**

^ matches the start of a string.

$ matches the end of a string.

^ABC matches the three first characters ABC DCE

DCE$ matches the three last characters in string ABC DCE

BC$ does not match ABC DCE because it is not at the end of the string

**Group patterns**

Parentheses allows you to group characters

W(AB){2} is the same as WABAB

In the beginning of this post I said that regular expressions are good for matching phone numbers.

The phone number I am looking for begins with three digits 0 to 9, the pattern becomes [0-9]{3}.

Next is a hyphen and then 6 more digits between 0 to 9, the pattern becomes -[0-9]{6} and combined [0-9]{3}-[0-9]{6}.

Count matching substrings using reg exp.xlsm

]]>

The excel 2016 formula lets you concatenate all values in one cell and it works only in excel 2016 because of the TEXTJOIN function. You only need one formula and it is small and easy to understand.

The version that works with all excel versions lets you put values in a cell each however the formulas are a little bit more complicated.

- Reverse two-way lookups in a cross reference table [Excel 2016]
- Reverse two-way lookups in a cross reference table [All excel versions]
- Reverse two-way lookup in a cross reference table
- Functions in this post

I have this table where there are two dependent variables.

table

Row 2 contains variable and Column B contains another variable

The combination of 32 with 51 gives me 0.9 or cell D4

I need to report which 2 and B combinations give me values between 0.4 and 0.5. In this case, it would be 31-53 and 32-50.

I could rearrange this to be in 3 columns with the results on the last column and use something like below (The equation does not represent the example I gave.)

IFERROR(INDEX(array, SMALL(IF((min=data), ROW($B$2:$B$10)-1), ROW(A3)), COLUMN(A3)), " ")

This works but my excel table would have too many rows and the initial data set comes the other way so it would take some time to convert it. The original matrix is more compact.

I am trying to convert this equation but I am having trouble matching it to an array instead to just one column.

table

Row 2 contains variable and Column B contains another variable

The combination of 32 with 51 gives me 0.9 or cell D4

I need to report which 2 and B combinations give me values between 0.4 and 0.5. In this case, it would be 31-53 and 32-50.

I could rearrange this to be in 3 columns with the results on the last column and use something like below (The equation does not represent the example I gave.)

IFERROR(INDEX(array, SMALL(IF((min=data), ROW($B$2:$B$10)-1), ROW(A3)), COLUMN(A3)), " ")

This works but my excel table would have too many rows and the initial data set comes the other way so it would take some time to convert it. The original matrix is more compact.

I am trying to convert this equation but I am having trouble matching it to an array instead to just one column.

The following array formula returns multiple values from a cross reference table if they meet specific critera.

**Array formula in cell H3:**

=TEXTJOIN(",", TRUE, IF(($C$3:$E$5>=$H$1)*($C$3:$E$5<=$H$2), $B$3:$B$5&"-"&$C$2:$E$2,""))

If you prefer having the values in a cell each instead of concatenated values in one cell, go to this part of this article.

If you did above steps correctly excel automatically adds a beginning and ending curly bracket to the formula, like this:

Don't enter these characters yourself.

Note, TEXTJOIN function works only in Excel 2016. If you have an earlier version of excel, use this formula.

*Step 1 - Check if values in cell range C3:E5 are larger than H1 and smaller than H2*

IF(($C$3:$E$5>=$H$1)*($C$3:$E$5<=$H$2), $B$3:$B$5&"-"&$C$2:$E$2,"")

becomes

IF({0,0,1;1,0,0;0,0,0}, $B$3:$B$5&"-"&$C$2:$E$2,"")

*Step 2 - If TRUE concatenate corresponding values in row 2 and column B*

IF({0,0,1;1,0,0;0,0,0}, $B$3:$B$5&"-"&$C$2:$E$2,"")

becomes

IF({0,0,1;1,0,0;0,0,0}, {"31-50","31-51","31-53";"32-50","32-51","32-53";"33-50","33-51","33-53"},"")

and returns

{"","","31-53";"32-50","","";"","",""}

*Step 3 - Concatenate values using TEXTJOIN function*

TEXTJOIN(",",TRUE,{"","","31-53";"32-50","","";"","",""})

returns

31-53. 32-50 in cell H3.

Link to two-way lookups in an index table:

Looking up data in a cross reference table

Reverse two-way lookup in a cross reference table.xlsx

This formula is for excel versions that don't have the TEXTJOIN function or if you prefer having the values in a cell each instead of concatenated values in one cell.

**Array formula in cell G7:**

=INDEX($B$3:$B$5, SMALL(IF(($C$3:$E$5<=$H$2)*($C$3:$E$5>=$H$1), MATCH(ROW($B$3:$B$5), ROW($B$3:$B$5)), ""), ROW(A1)))

**Array formula in cell H7:**

=INDEX($C$2:$E$2, SMALL(IF(($C$3:$E$5<=$H$2)*($C$3:$E$5>=$H$1)*(G7=$B$3:$B$5) ,MATCH(COLUMN($C$2:$E$2), COLUMN($C$2:$E$2)), ""), COUNTIF($G$6:G7,G7)))

**Array formula in cell I7:**

=INDEX($C$3:$E$5, MATCH(G7,$B$3:$B$5, 0), MATCH(H7, $C$2:$E$2, 0))

Reverse-two-way-lookup-in-a-cross-reference-tablev3.xlsx

The following array formulas returns a single value from a cross reference table.

**Array formula in cell E15:**

=INDEX($A$3:$A$12, MIN(IF(B3:K12=E14, MATCH(ROW(B3:K12), ROW(B3:K12)), "")))

**Array formula in cell E16:**

=INDEX($B$2:$K$2, MIN(IF(B3:K12=E14, MATCH(COLUMN(B3:K12), COLUMN(B3:K12)), "")))

*Step 1 - Check what values in cell range equals value in E14 and return smallest row*

MIN(IF(B3:K12=E14, MATCH(ROW(B3:K12), ROW(B3:K12)), ""))

returns 5.

**Step 2 - Return corresponding header value**

=INDEX($A$3:$A$12, 5)

returns H.

Reverse two-way lookup in a cross reference tablev2.xlsx

**TEXTJOIN**(*delimiter*, *ignore_empty*, *text1*, ...)

Concatenates a list or range of text strings using a delimiter

**IF**(*logical_test*, [*value_if_true*], [*value_if_false*])

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**ROW(**reference**)**

Returns the rownumber of a reference

The map chart below shows US states by population and I will show how I made this.

I copied US population data from wikipedia and rounded values to millions with one decimal.

This world map shows up, US states are barely visible. This is not what we want.

Double click on the map with left mouse button to access chart formatting.

Here we have three options we can modify, map projection, map area and map labels.

Map projection

*Automatic -*Self explanatory*Mercator -*Cylindrical map projection*Miller -*Modified Mercator projection*Robinson -*Showing the globe as a flat image

Map area

- Automatic
- Only regions with data
- World

Select "Only regions with data" and the chart changes to this:

Still not good, US state Alaska is so big it is hard to see the other states.

However, this setting shows different options if we add a third column to our data. Column A has value US in all 52 cells, see picture below.

Now change the data source to include column A. Right click on chart and select "Select Data...". Change the cell reference to A1:C56

Click OK button. The map chart changes once again.

Lets look at the formatting options. There are now five different map areas and the "Automatic" setting shows the above map chart.

- Automatic
- Only regions with data
- Country/Region
- Multiple countries/regions
- World

Map labels setting lets you display state names: None, best fit only or show all. I have selected "Show all" and this is what the map chart looks like.

If you resize the chart, even more state names are visible. You can also add the population number, right click on map and click on "Add Data Labels", see chart below.

If you don't like the state colors you can change that, as well. Double click on the map and then click on a state, the format object task pane appears to the right hand side of the excel window, see picture below.

Click "Series Options" and then "Series Color" to see map chart color settings. Click the "Fill color" buttons and pick a lowest value color and a highest value color.

]]>

I have

A1(anil singh raj)

It can be anything Like

A1(singh raj anil)

I want return value in

B1 (10 30 20)

Or

B1(30 20 10)

Or

Lookup array is

D1(anil) E1(10)

D2(raj) E2(20)

D3(singh) E3(30)

A1(anil singh raj)

It can be anything Like

A1(singh raj anil)

I want return value in

B1 (10 30 20)

Or

B1(30 20 10)

Or

Lookup array is

D1(anil) E1(10)

D2(raj) E2(20)

D3(singh) E3(30)

I have made a small custom function to split the search string and get the values you are looking for.

**Formula in cell B1:**

=SearchValues(A1,$D$1:$D$3,$E$1:$E$3)

Function SearchValues(str As Range, search_col As Range, return_col As Range) Dim j As Long, i As Long arr = Split(str, " ") j = search_col.Rows.CountLarge For Each Vl In arr For i = 1 To j If search_col.Cells(i, 1) = Vl Then result = result & return_col.Cells(i, 1) & " " Next i Next Vl SearchValues = result End Function

You need to copy the code above and paste it to a code module, detailed instructions below.

- Open vb editor (shortcut keys: Alt+F11)
- Insert a new module
- Paste code to code module
- Exit vb Editor

Split search value using delimiter and search for each string.xlsm

]]>