The list in column D contains only values that are unique, Aa and BB exists only once in the list, all other values have a duplicate.

The formula extracts CC, bb, aa and Cc because they exist only once in column B.

**Array formula in cell D3:**

=INDEX($B$3:$B$15, SMALL(IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""),ROWS($A$1:A1)))

If you are looking for a unique distinct list, read this post: Extract unique distinct values (case sensitive) [Formula].

This post explains how to do a case-sensitive VLOOKUP and returning multiple values:

**Search case sensitive and return multiple values in excel**

Comments(0) Filed in category: Case sensitive, Excel, VLOOKUP and return multiple values

Make sure you read the following article if you want to extract duplicate values:

**Extract a list of duplicates from a column**

Comments(41) Filed in category: Duplicate values, Excel

- Copy (Ctrl + c) above formula
- Double click on cell C2
- Paste (Ctrl + v) to cell C2
- Press and hold CTRL + SHIFT simulatneously
- Press Enter once
- Release all keys

Your formula now looks like this: {=array_formula}

Don't enter the curly brackets, they appear automatically.

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Built-in features, Count values, Excel

You can easily follow along if you download the attached file and select cell D3. Then go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

Click "Evaluate" button shown in above picture to move to next step.

The EXACT function is case sensitive function that allows you to compare values. If they match EXACT returns TRUE, if not FALSE.

EXACT($B$3:$B$15,TRANSPOSE(B$3:$B$15))*1

If we use TRANSPOSE we can compare values against each other to build an array, in a single calculation. The following picture shows you this array as an index table, I have highlighted cells that match green. Example, cell C3 shows you the result of a comparison between the value in cell C2 and B3. Since it is the same value they must match and the formula returns 1 and is highlighted green.

It is now obvious that value Aa has a duplicate because cell J3 is also highlighted green.

Incredible that Excel allows you to do such a complicated calculation in a single cell.

Recommended reading

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Comments(4) Filed in category: Excel, Functions, Transpose

The MMULT function is like the SUMPRODUCT function but on steroids, let me explain. SUMPRODUCT lets you multiply and then sum values, the result is a single value.

MMULT lets you multiply and sum values either column-wise or row-wise, the result is an array.

MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0) is entered in column P, see picture below.

It is now easy to spot unique values in the index table, if column P contains 1 the corresponding value in column B must be unique.

Recommended reading

**MMULT function – Matrix multiplication**

Comments(4) Filed in category: Excel, Functions, Mmult

Column Q shows you corresponding relative row number if value in column P is equal to 1.

IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), "")

returns this array: {"";2;"";""; "";"";7; "";9;"";11; "";""}

Recommended reading

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel, Functions

SMALL(IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""),ROWS($A$1:A1))

becomes

SMALL({"";2;"";""; "";"";7; "";9;"";11; "";""},ROWS($A$1:A1))

becomes

SMALL({"";2;"";""; "";"";7; "";9;"";11; "";""},1)

and returns 2.

Recommended reading

**SMALL function and LARGE function**

Comments(12) Filed in category: Excel, Functions

INDEX($B$3:$B$15, SMALL(IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""),ROWS($A$1:A1)))

becomes

INDEX($B$3:$B$15, 2)

becomes

INDEX({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"; "AA"; "Cc"; "EE"; "BB"}, 2)

and returns "CC" in cell D3.

Recommended reading

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

The image below shows a table in column B and C. Column B contains names and column C contains products. How many unique distinct products did Salesperson Jennifer sell?

The blue arrows shows unique distinct products based on sales person "Jennifer", the total number match with the number in cell E4. The remaining highlighted records are only duplicate values.

**Array formula in cell E2:**

=SUM(IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26)), 0))

Learn to build formulas that count unique distinct values based on criteria:

**Count unique distinct values that meet multiple criteria**

Comments(93) Filed in category: Count unique distinct values, Count values, Excel

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

Recommended article:

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Built-in features, Count values, Excel

You can follow along as I explain the formula, select cell E2. Go to tab "Formulas" on the ribbon, click on "Evaluate formula" button.

Click on "Evaluate" button shown in above image to move to next step.

1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26))

becomes

1/{4; 2; 3; 4; 0; 3; 4; 3; 4; 3; 0; 3; 2; 3; 2; 2; 2; 4; 4; 4; 4; 3}

and returns

{0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333; 0,25; 0,333333333333333; 0,25; 0,333333333333333; #DIV/0!; 0,333333333333333; 0,5; 0,333333333333333; 0,5; 0,5; 0,5; 0,25; 0,25; 0,25; 0,25; 0,333333333333333}

Recommended article:

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

Comments(1) Filed in category: Excel, Functions

IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26)), 0)

becomes

IF({TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333; 0,25; 0,333333333333333; 0,25; 0,333333333333333; #DIV/0!; 0,333333333333333; 0,5; 0,333333333333333; 0,5; 0,5; 0,5; 0,25; 0,25; 0,25; 0,25; 0,333333333333333}, 0)

and returns

{0,25; 0,5; 0,333333333333333; 0; 0; 0; 0,25; 0,333333333333333; 0; 0; 0; 0,333333333333333; 0,5; 0; 0; 0,5; 0,5; 0; 0,25; 0; 0,25; 0}

Recommended article:

Comments(9) Filed in category: Excel, Functions

=SUM(IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26)), 0))

becomes

=SUM({0,25; 0,5; 0,333333333333333; 0; 0; 0; 0,25; 0,333333333333333; 0; 0; 0; 0,333333333333333; 0,5; 0; 0; 0,5; 0,5; 0; 0,25; 0; 0,25; 0})

and returns 4 in cell E2.

Recommended article:

The SUM function in excel allows you to add values, the function returns the sum in the cell it is […]

Comments(0) Filed in category: Excel, Functions, Sum

I have a set of data, like the one you used in the original example that also has a column for the date of the transaction. I would like my Index-type formula to search for both the main item (the rep's name) and also if the date of the transaction falls in the date range).

Start Date: 11/26/2012

End Date: 11/30/2012 (both entered by the user)

Rep: John

Then the results, in each row/column of the 'result' section (INDEX formula results) would show results for John that occurred from 11/26 to 11/30 (including both dates).

Thanks for any help with the formula for that.

Jason

**Answer:**

**Array formula in cell B18:**

=INDEX($C$2:$C$11, SMALL(IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13), MATCH(ROW($A$2:$A$11), ROW($A$2:$A$11)), ""), ROW(A1)))

**Lookup multiple values in different columns and return a single value**

Comments(31) Filed in category: Excel, Vlookup

- Copy above array formula
- Double click on cell E2
- Paste array formula
- Press and hold Ctrl + Shift simultaneously
- Press Enter once
- Release all keys

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Built-in features, Count values, Excel

- Select cell E2
- Copy cell (Ctrl + c)
- Select cell E3:E20
- Paste (Ctrl + v)

You can easily follow along, select cell B18. Go to tab "Formulas" and click "Evaluate formula" button.

Click "Evaluate" button, shown in above picture, to move to next step.

$B$15=$B$2:$B$11

becomes

"John"={"John";"Jennifer";"Laura";"Paul";"John";"Laura";"Jennifer";"Paul";"Paul";"John"}

and returns

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

($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13)

becomes

({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE})*({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE})

and returns

{1;1;1;1;1;1;1;0;0;0}

IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11))

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}*{1;1;1;1;1;1;1;0;0;0},MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11))

becomes

IF({1;0;0;0;1;0;0;0;0;0},{1; 2; 3; 4; 5; 6; 7; 8; 9; 10})

and returns

{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE}

Comments(9) Filed in category: Excel, Functions

SMALL(IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)),""),ROW(A1))

becomes

SMALL({1; FALSE; FALSE; FALSE; 5; FALSE; FALSE; FALSE; FALSE; FALSE},ROW(A1))

becomes

SMALL({1; FALSE; FALSE; FALSE; 5; FALSE; FALSE; FALSE; FALSE; FALSE},1)

and returns 1.

**SMALL function and LARGE function**

Comments(12) Filed in category: Excel, Functions

INDEX($C$2:$C$11,SMALL(IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)),""),ROW(A1)))

becomes

INDEX($C$2:$C$11,1)

becomes

INDEX({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"},1)

and returns A in cell B18.

Comments(14) Filed in category: Excel, Functions, Index

Lookup multiple values in different columns and return multiple valuesv2.xlsx

]]>"Fishing rod" is a duplicate value in column C but it is not in category "Winter", however "Skates" and "Sledge" are duplicates and in category "Winter.

**Array formula in cell E5:**

=INDEX($C$3:$C$9, MATCH(SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1)), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0))

The following article shows you how to filter unique distinct values sorted alphabetically and based on a condition:

**Unique distinct list sorted alphabetically and based on a condition**

Comments(1) Filed in category: Excel, Sorted unique distinct values, VLOOKUP and return multiple values

- Copy (Ctrl + c) above formula
- Double click on cell E5
- Paste formula to cell E5
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys

The formula now looks like this: {=*arrayformula*}

Don't enter the curly brackets yourself, they appear automatically.

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Built-in features, Count values, Excel

You can easily follow along, select cell E5. Go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

Then click "Evaluate" button to move to next step.

COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1

becomes

COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1

becomes

COUNTIFS({"Winter"; "Winter"; "Winter"; "Summer"; "Winter"; "Summer"; "Winter"},"Winter",{"Skates"; "Ski"; "Skates"; "Fishing rod"; "Sledge"; "Fishing rod"; "Sledge"},{"Skates"; "Ski"; "Skates"; "Fishing rod"; "Sledge"; "Fishing rod"; "Sledge"})>1

and returns

{2;1;2;0;2;0;2}

Comments(1) Filed in category: Excel, Functions

This allows us to filter duplicate values.

COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1

becomes

{2;1;2;0;2;0;2}>1

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

COUNTIF($E$4:E4, $C$3:$C$9)

returns {0;0;0;0;0;0;0}

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel, Functions

This makes sure that previous values in column E are not repeated.

COUNTIF($E$4:E4, $C$3:$C$9)>0

becomes

{0;0;0;0;0;0;0}=0

and returns {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

(COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0)

becomes

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

and returns {1;0;1;0;1;0;1}

COUNTIF($C$3:$C$9, "<"&$C$3:$C$9) returns {2;4;2;0;5;0;5}

IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), "")

becomes

IF({1;0;1;0;1;0;1},{2;4;2;0;5;0;5}, "")

and returns {2;"";2;"";5;"";5}

Comments(9) Filed in category: Excel, Functions

SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1))

becomes

SMALL({2;"";2;"";5;"";5}, ROWS($A$1:A1))

becomes

SMALL({2;"";2;"";5;"";5}, 1)

and returns 2.

**SMALL function and LARGE function**

Comments(12) Filed in category: Excel, Functions

MATCH(SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1)), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0)

becomes

MATCH(2, COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0)

becomes

MATCH(2, {2;4;2;0;5;0;5}, 0)

and returns 1.

Identify the position of a value in an array.

Comments(12) Filed in category: Excel, Functions

INDEX($C$3:$C$9, MATCH(SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1)), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0))

becomes

INDEX($C$3:$C$9, 1)

becomes

INDEX({"Skates";"Ski";"Skates";"Fishing rod";"Sledge";"Fishing rod";"Sledge"}, 5)

and returns "Skates" in cell E5.

Comments(14) Filed in category: Excel, Functions, Index

Filter duplicates sorted from A to Z based on a condition.xlsx

]]>**Formula in cell E2:**

=IFERROR(LOOKUP(2, 1/((COUNTIF($A$2:$A$20, $A$2:$A$20)>1)*(COUNTIF($E$1:E1, $A$2:$A$20)=0)*(COUNTIF($C$2:$C$3, $A$2:$A$20)<>1)), $A$2:$A$20), "")

This post explains how to extract duplicate values using a condition:

**Create a list of duplicates where adjacent cell value meets a condition**

Comments(0) Filed in category: Duplicate values, Excel

You can also use more than one condition, this article shows you how:

**Filter duplicate values using critera**

Comments(0) Filed in category: Duplicate values, Excel

If you want a list sorted from A to Z, read this article:

**Extract a list of alphabetically sorted duplicates from a column**

Comments(1) Filed in category: Duplicate values, Excel, Sort values

Filter a unique distinct list:

**5 easy ways to extract unique distinct values**

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

how-to-extract-a-list-of-duplicates-from-a-columns-in-excel_version4.xlsx

]]>The result is displayed in cell A6 and below.

**Array formula in cell A6:**

=INDEX($F$2:$F$21, SMALL(IFERROR(IF(MATCH(IF(($D$2:$D$21<=$B$2)*($D$2:$D$21>=$B$1)*($E$2:$E$21=$B$3),$F$2:$F$21,""), IF(($D$2:$D$21<=$B$2)*($D$2:$D$21>=$B$1)*($E$2:$E$21=$B$3), $F$2:$F$21,"A"), 0)<>MATCH(ROW($F$2:$F$21), ROW($F$2:$F$21)), MATCH(ROW($F$2:$F$21), ROW($F$2:$F$21))), ""), ROW(A1)))

If you want a list of duplicates, sorted from A to Z, read this article:

**Extract a list of alphabetically sorted duplicates from a column**

Comments(1) Filed in category: Duplicate values, Excel, Sort values

This post explains how to extract duplicate values using a condition:

**Create a list of duplicates where adjacent cell value meets a condition**

Comments(0) Filed in category: Duplicate values, Excel

Filter a unique distinct list:

**5 easy ways to extract unique distinct values**

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

Extract a list of duplicates:

**Extract a list of duplicates from a column**

Comments(41) Filed in category: Duplicate values, Excel

- Copy (Ctrl + c) above formula
- Double click on cell A6
- Paste (Ctrl + v) to cell A6
- Press and hold CTRL + SHIFT simulatneously
- Press Enter once
- Release all keys

Your formula now looks like this: {=array_formula}

Don't enter the curly brackets, they appear automatically.

I highly recommend using the TEXTJOIN function if you own Excel 2016. It is what the CONCATENATE function should have been from the beginning:

**Lookup and return multiple values concatenated into one cell**

Comments(249) Filed in category: Concatenate, Excel, Textjoin

- Copy (CTRL + c) this formula:

=TRANSPOSE(IF(B3:B11=F3,C3:C11,"")) - Double click on cell F4
- Paste (Ctrl + v) formula to cell F4
- Select the entire formula in the formula bar

- Press Function key F9 and the formula is converted to an array of constants:

={"","CD","","","IJ","","","OP",""} - Delete the equal sign = in the formula bar and then press Enter

{"","CD","","","IJ","","","OP",""} - Select cell F4
- I am now going to delete all empty characters in the array, press CTRL + H to open "Search and Replace" Dialog box

- Type in Find what: "", and nothing in Replace with:
- Click on "Replace" button

- Type in Find what: ,"" and nothing in Replace with:
- Click "Replace" button once again
- Click Close button

The final thing to do is to use the concatenate function to add all values into one text string.

- Double click on cell F4
- Delete the curly brackets from the formula: {}
- Add this: =CONCATENATE(
- and then an ending parentheses )

- Press Enter

Perhaps you want a delimiting character between values, right after you have converted the formula to an array of constants (step 5, above), do this:

- The array looks like this: ={"","CD","","","IJ","","","OP",""}
- Add an ampersand and then a delimiting character with quotation marks.

={"","CD","","","IJ","","","OP",""}&"|" - Select the formula and press function key F9, the formula now looks like this:

={"|","CD|","|","|","IJ|","|","|","OP|","|"}

The ampersand has added the delimiting character to all values in the array. - Continue with step 6 above.
**Tip!**To delete empty values in array, Search and Replace with these values "|", and ,"|"

If you think the above instructions are too tedious and want something quicker and easier, check out this UDF. You can find it near the bottom of this article:

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

Comments(4) Filed in category: Excel, Functions, Textjoin

Concatenate a cell range using a condition.xlsx

Recommended article

**Quickly concatenate values into one cell [No VBA]**

Comments(86) Filed in category: Concatenate, Excel, Textjoin

**Formula in cell D3:**

=LOOKUP(2,1/(B:B<>""),B:B)

The formula is quite cpu-intensive since it is processing all cells in column B, there are more than a million cells in one column in Excel 2007 and later versions.

Recommended article

**Find last matching value in an unsorted list**

Comments(22) Filed in category: Excel, Lookup function

You can change the cell references if you know you will have a smaller list, this formula is easier for Excel to process:

=LOOKUP(2,1/(B1:B10000<>""),B1:B10000)

The formula takes all cell values in column B and checks if they are not empty. Then it divides 1 with the array creating errors for all empty cells.

The LOOKUP function ignores errors and tries to find a match. If every match is 1 and the lookup value is 2, the LOOKUP function returns last value in cell range. Normally the list must be sorted ascending for the LOOKUP function to work, however since every value is 1 there is no need to sort the list.

I am going to use the following formula because it will be easier to demonstrate:

LOOKUP(2,1/(B3:B12<>""),B3:B12)

**Step 1 - Check if cells in column B are not equal to nothing**

B3:B12<>""

becomes

{"ZM";"TY";"NX";0; "DY";"RQ";0; "JW";"XH";0}<>""

and returns

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

**Step 2 - Divide 1 with array**

1/(B3:B12<>"")

becomes

1/({TRUE;TRUE;TRUE; FALSE;TRUE;TRUE;FALSE; TRUE;TRUE;FALSE})

and returns

{1;1;1;#DIV/0!;1;1; #DIV/0!;1;1;#DIV/0!}

Boolean value TRUE is equal to 1 and FALSE is equal to 0. You can't divide a value with zero so Excel returns an error (#DIV/0!).

**Step 3 - Return value**

LOOKUP(2,1/(B3:B12<>""),B3:B12)

becomes

LOOKUP(2,{1;1;1;#DIV/0!;1;1; #DIV/0!;1;1;#DIV/0!},B3:B12)

becomes

LOOKUP(2,{1;1;1;#DIV/0!;1;1; #DIV/0!;1;1;#DIV/0!},{"ZM";"TY";"NX";0; "DY";"RQ";0; "JW";"XH";0})

and returns XH in cell D3.

Recommended article

Finds a value in a sorted cell range and returns a value on the same row.

Comments(0) Filed in category: Excel, Lookup function

I have added a few errors in column B below.

I am working with Excel 2016 and errors in column B seems to not be an issue.

This data set has two columns, column B and C.

**Formula in cell E3:**

=LOOKUP(2,1/(B:B<>""),C:C)

The formula returns an adjacent value of the last value. In fact, it doesn't need to be adjacent, you can change the cell reference (C:C) as long as it's starting point and ending point is the same as the first cell reference (B:B).

**Array formula in cell E6:**

=INDEX($C$3:$C$11, MATCH(SMALL(IF((COUNTIF($E$5:E5, $C$3:$C$11)=0)*($B$3:$B$11=$E$3), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), ""), 1), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), 0))

Recommended post

**Sorting numbers and text cells also removing blanks using an array formula**

Comments(22) Filed in category: Excel

- Double click on cell E6
- Copy (Ctrl + c) and paste (Ctrl + v) array formula to cell
- Press and hold Ctrl + Shift simultaneously
- Press Enter once.
- Release all keys.

There are now a beginning and ending curly bracket in the formula bar, like this: {=formula}

Don't enter these characters yourself.

Read my explanation here: Create a unique distinct alphabetically sorted list, extracted from a column

Recommended reading:

**Extract and sort text cells from a range containing both numerical and text values**

Comments(1) Filed in category: Excel, Sort values

Create a unique distinct alphabetically sorted list with criteria.xlsx

]]>Ok, you've shown it for regular ranges....how about within tables.

I have a table similar to:

ID Name Date

1001 Joe Smith 5/1/2017

1002 John Doe 5/2/2017

1001 Joe Smith 5/17/2017

1003 Jane Doe 5/18/2017

1001 Joe Smith 5/20/2017

The formula below lets you search for criteria and return the last matching record in the table.

Example, 1001 and Joe Smith is found on row 3,5 and 7. The record on row 7 is the last record in the table so the formula returns the date (2017-05-17) from row 7, in cell G4.

**Formula in cell G4:**

=LOOKUP(2,1/((B3:B12=G2)*(C3:C12=G3)),D3:D12)

This formula is weird, you don't need to enter it as an array formula as you should. I don't know why, however this gave me an idea, check it out here:

Create a list of unique distinct values

Recommended article

**Find last matching value in an unsorted list**

Comments(22) Filed in category: Excel, Lookup function

**Step 1 - Construct first logical expression (ID)**

We want to find all to find values in column B equal to 1001.

B3:B12=G2

becomes

{1001;1002;1001;1003;1001;1003;1002;1002;1003;1002}=1001

and returns

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

**Step 2 - Construct second logical expression (NAME)**

The second logical expressions checks if values in cell range C3:C12 is equal to "Joe Smith"

C3:C12=G3

becomes

{"Joe Smith";"John Doe";"Joe Smith";"Jane Doe";"Joe Smith";"Jane Doe";"John Doe";"John Doe";"Jane Doe";"John Doe"}="Joe Smith"

and returns

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

**Step 3 - Add arrays**

Both conditions must be met so we must multiply (*) the arrays. If we wanted at least one of two conditions met we would add the arrays (+)

(B3:B12=G2)*(C3:C12=G3)

becomes

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

and returns

{1;0;1;0;1;0;0;0;0;0}

**Step 4 - Dividing by zero returns an error**

The LOOKUP function allows us to match a value if it is sorted ascending, however it also lets you match the last value in an array if the others are errors.

1/((B3:B12=G2)*(C3:C12=G3))

becomes

1/{1;0;1;0;1;0;0;0;0;0}

and returns

{1;#DIV/0!;1;#DIV/0!; 1;#DIV/0!; #DIV/0!; #DIV/0!;#DIV/0!; #DIV/0!}

1/0 returns #DIV/0!

**Step 5 - Find last value in array**

The LOOKUP function finds the last value in the array and returns the corresponding value on the same row in cell range D3:D12.

LOOKUP(2,1/((B3:B12=G2)*(C3:C12=G3)),D3:D12)

becomes

LOOKUP(2,{**1**;#DIV/0!;**1**;#DIV/0!; **1**;#DIV/0!; #DIV/0!; #DIV/0!;#DIV/0!; #DIV/0!},D3:D12)

becomes

LOOKUP(2,{1;#DIV/0!;1;#DIV/0!; 1;#DIV/0!; #DIV/0!; #DIV/0!;#DIV/0!; #DIV/0!},{**42856**;42857;**42875**; 42873;**42872**;42858; 42877;42857;42860; 42882})

and returns 2017-05-17 (42872) in cell G4.

Comments(0) Filed in category: Excel, Lookup function