Array formula in B14:

=INDEX(Product, SMALL(IF(($C$10=ID_num)*(OrderDate=$C$11), ROW(Product)-MIN(ROW(Product))+1), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges

ID_num (B3:B6)
OrderDate (C3:C6)
Product (D3:D6)
What is named ranges?

Download excel file for this tutorial.

Lookup two index columns returning multiple matches.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

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

Related articles:

• Lookup two index columns in excel

Related posts:

1. Lookup two index columns in excel
2. Lookup with multiple criteria and display multiple search results using excel formula, part 3
3. Lookup with multiple criteria and display multiple search results using excel formula, part 2
4. Lookup with multiple criteria and display multiple search results using excel formula, part 4
5. Lookup with multiple criteria and display multiple search results using excel formula
6. Lookup Unique based on Multiple Conditions
7. A more userfriendly version of: Automatically filter unique row records from multiple columns
8. Automatically filter unique row records from multiple columns
9. Unique distinct values from multiple columns using array formula
10. How to return multiple values using vlookup in excel

like if data is numaric we put formula =MIN(E5:E20) but in this case datais text

Answer: I am not sure I understand but I will try to give you an answer.

Array formula in A1:

=INDEX(A1:A100, MIN(IF(ISBLANK(A2:A100), "", ROW(A2:A100)))) + CTRL + SHIFT + ENTER copied right as far as needed.

Functions in this article:

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

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

ISBLANK(value)
Checks whether a reference is to an empty cell and returns TRUE or FALSE

Related posts:

1. Create a list of all numbers or text in a column in excel
2. Identify largest text value in a column using array formula in excel
3. Multiple cells in a new row
4. Unique list to be created from a column where an adjacent column has text cell values
5. Create a unique distinct text list from a range containing both numerical and text values in excel
6. Extract duplicate text values from a range containing both numerical and text values in excel
7. Filter unique text values from a range containing both numerical and text values in excel
8. Extract and sort text cells from a range containing both numerical and text values
9. Extract all rows from a range that meet criteria in one column in excel
10. Create unique distinct list from column where an adjacent column meets criteria

Formula in B14:

=INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)) + ENTER

Alternative array formula in B15:

=INDEX(Product, MATCH(C10&"-"&C11, ID_num&"-"&OrderDate, 0)) + CTRL + SHIFT + ENTER

Alternative array formula in B16:

=INDEX(Product, MIN(IF((C10=ID_num)*(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1))) + CTRL + SHIFT + ENTER

Named ranges

ID_num (B3:B6)
OrderDate (C3:C6)
Product (D3:D6)
What is named ranges?

Download excel file for this tutorial.

Lookup two index columns.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

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

SUMPRODUCT(array1;array2;)
Returns the sum of the products of the corresponding ranges or arrays

Related articles:

• Lookup two index columns returning multiple matches in excel

Related posts:

1. Lookup two index columns returning multiple matches in excel
2. Lookup with multiple criteria and display multiple search results using excel formula, part 4
3. Lookup with multiple criteria and display multiple search results using excel formula, part 3
4. Merge two columns into one list in excel
5. How to filter values between 0.5 and 1.5 from two columns in excel 2007
6. Lookup Unique based on Multiple Conditions
7. Lookup with multiple criteria and display multiple search results using excel formula
8. Extract largest values from two columns using array formula in excel
9. Lookup with multiple criteria and display multiple search results using excel formula, part 2
10. Identify missing values in two columns using excel formula

Answer:

Array formula in G12:

=SUM(IF(FREQUENCY(COUNTIF(List2, "<"&List2), COUNTIF(List2, "<"&List2))>0, IF(COUNTIF(List1, List2)>0, 1, 0), "")) + CTRL + SHIFT + ENTER

Array formula in G25:

=SUM(IF(COUNTIF(List1, List2)>0, 1, 0)) + CTRL + SHIFT + ENTER

Array formula in G38:

=SUM(IF(COUNTIF(List2, List1)>0, 1, 0)) + CTRL + SHIFT + ENTER

Named ranges

List1 (B3:B9)
List2 (B7:D9)
What is named ranges?

How to implement array formula to your workbook

Change the named ranges.

Download excel file for this tutorial.

count matching cell values in two columns in excel.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

SUM(number1,[number2],)
Adds all the numbers in a range of cells

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

Related posts:

1. Count unique distinct values in two columns with date criteria in excel
2. Extract largest values from two columns using array formula in excel
3. Count unique distinct values in two columns in excel
4. Comparing two columns and sum unique values using array formula in excel
5. Count unique distinct values in three columns combined in excel
6. Identify missing values in two columns using excel formula
7. Count unique distinct text values in a range in excel
8. Count unique distinct values in a column in excel
9. Filter common values from three columns in excel
10. Merge two columns into one list in excel

Answer:

Array formula in A4:

=TEXT(IF(MOD(COLUMN(A:A),  2),  DATE(2009,  ROUND(COLUMN(A:A)/2,  0),  1),  DATE(2009,  ROUND(COLUMN(A:A)/2,  0),  16)),  "MM/DD/YY")&"-"&TEXT(IF(MOD(COLUMN(A:A),  2),  DATE(2009,  ROUND(COLUMN(A:A)/2,  0),  15),  DATE(2009,  ROUND(COLUMN(A:A)/2,  0)+1,  1)-1),  "MM/DD/YY") + ENTER copied rigt as far as needed.

Array formula in A7:

=TEXT(IF(NOT(MOD(COLUMN(B:B), 2)), DATE(2009, 2-ROUND(COLUMN(B:B)/2, 0), 1), DATE(2009, 2-ROUND(COLUMN(B:B)/2, 0), 16)), "MM/DD/YY")&"-"&TEXT(IF(NOT(MOD(COLUMN(B:B), 2)), DATE(2009, 2-ROUND(COLUMN(B:B)/2, 0), 15), DATE(2009, 2-ROUND(COLUMN(B:B)/2, 0)+1, 1)-1), "MM/DD/YY") + ENTER copied rigt as far as needed.

Array formula in A10:

=TEXT(IF(MOD(ROW(1:1), 2), DATE(2009, ROUND(ROW(1:1)/2, 0), 1), DATE(2009, ROUND(ROW(1:1)/2, 0), 16)), "MM/DD/YY")&"-"&TEXT(IF(MOD(ROW(1:1), 2), DATE(2009, ROUND(ROW(1:1)/2, 0), 15), DATE(2009, ROUND(ROW(1:1)/2, 0)+1, 1)-1), "MM/DD/YY") + ENTER copied down as far as needed.

Array formula in A18:

=TEXT(IF(NOT(MOD(ROW(2:2), 2)), DATE(2009, 2-ROUND(ROW(2:2)/2, 0), 1), DATE(2009, 2-ROUND(ROW(2:2)/2, 0), 16)), "MM/DD/YY")&"-"&TEXT(IF(NOT(MOD(ROW(2:2), 2)), DATE(2009, 2-ROUND(ROW(2:2)/2, 0), 15), DATE(2009, 2-ROUND(ROW(2:2)/2, 0)+1, 1)-1), "MM/DD/YY") + ENTER copied down as far as needed.

Functions in this article:

ROW(reference) returns the rownumber of a reference

DATE(year,month,day) returns the number that represents the date in Microsoft Office Excel date-time code

TEXT(value, format_text)
Converts a value to text in a specific number format

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

Related articles:

• Create a weekly date range using excel formula
• Create a quartely date range in excel
• Create a monthly date range in excel

Related posts:

1. Create a quartely date range in excel
2. Create a monthly date range in excel
3. Create a date range using excel formula
4. Advanced custom date filter in Excel 2007
5. Create a unique distinct list from a date range in excel
6. Create a list of dates with blanks between quarters in excel
7. Extract dates and adjacent value in a range using a date critera in excel
8. Finding the nearest date in a range of dates using excel formula
9. Count unique distinct records in a date range and a numeric range in excel
10. Filter text values existing in range 1 but not in range 2 using array formula in excel

Array formula in A4:

=TEXT(DATE(2009, IF(COLUMN(A:A)=1, 1, COLUMN(A:A)*3-2),  1),  "MM/DD/YY")&"-"&TEXT(DATE(2009, COLUMN(A:A)*3+1,  1)-1,  "MM/DD/YY") + ENTER copied right as far as necessary.

Array formula in A7:

=TEXT(DATE(2009, IF(COLUMN(A:A)=1, 1, 1-(COLUMN(A:A)-1)*3),  1),  "MM/DD/YY")&"-"&TEXT(DATE(2009, IF(COLUMN(A:A)=1, 4, 0-(COLUMN(A:A)-2)*3+1),  1)-1,  "MM/DD/YY") + ENTER copied right as far as necessary.

Array formula in A10:

=TEXT(DATE(2009, IF(ROW(1:1)=1, 1, ROW(1:1)*3-2),  1),  "MM/DD/YY")&"-"&TEXT(DATE(2009, ROW(1:1)*3+1,  1)-1,  "MM/DD/YY") + ENTER copied down as far as necessary.

Array formula in A17:

=TEXT(DATE(2009, IF(ROW(1:1)=1, 1, 1-(ROW(1:1)-1)*3),  1),  "MM/DD/YY")&"-"&TEXT(DATE(2009, IF(ROW(1:1)=1, 4, 0-(ROW(1:1)-2)*3+1),  1)-1,  "MM/DD/YY") + ENTER copied down as far as necessary.

Functions in this article:

ROW(reference) returns the rownumber of a reference

DATE(year,month,day) returns the number that represents the date in Microsoft Office Excel date-time code

TEXT(value, format_text)
Converts a value to text in a specific number format

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

Related articles:

• Create a weekly date range in excel
  
• Create a monthly date range in excel
• Create a custom date range in excel

Related posts:

1. Create a monthly date range in excel
2. Create a date range using excel formula
3. Create a custom date range in excel
4. Create a unique distinct list from a date range in excel
5. Extract dates and adjacent value in a range using a date critera in excel
6. Create a unique distinct text list from a range containing both numerical and text values in excel
7. Finding the nearest date in a range of dates using excel formula
8. Count unique distinct records in a date range and a numeric range in excel
9. Sort dates within a date range using excel array formula
10. Filter duplicate rows and sort by date using array formula in excel

Formula in A4:

=TEXT(DATE(2009,  COLUMN(A:A),  1),  "MM/DD/YY")&"-"&TEXT(DATE(2009, COLUMN(A:A)+1,  1)-1,  "MM/DD/YY") + ENTER copied right as far as necessary.

Formula in A7:

=TEXT(DATE(2009,  2-COLUMN(A:A),  1),  "MM/DD/YY")&"-"&TEXT(DATE(2009, 2-COLUMN(A:A)+1,  1)-1,  "MM/DD/YY") + ENTER copied right as far as necessary.

Formula in A10:

=TEXT(DATE(2009,  ROW(1:1),  1),  "MM/DD/YY")&"-"&TEXT(DATE(2009, ROW(1:1)+1,  1)-1,  "MM/DD/YY") + ENTER copied down as far as necessary.

Formula in A19:

=TEXT(DATE(2009,  2-ROW(1:1),  1),  "MM/DD/YY")&"-"&TEXT(DATE(2009, 2-ROW(1:1)+1,  1)-1,  "MM/DD/YY") + ENTER copied down as far as necessary.

Functions in this article:

ROW(reference) returns the rownumber of a reference

DATE(year,month,day) returns the number that represents the date in Microsoft Office Excel date-time code

TEXT(value, format_text)
Converts a value to text in a specific number format

Related articles:

• Create a weekly date range using excel formula
• Create a quartely date range in excel
• Create a custom date range in excel

Related posts:

1. Create a quartely date range in excel
2. Create a date range using excel formula
3. Create a custom date range in excel
4. Create a unique distinct list from a date range in excel
5. Count unique distinct records in a date range and a numeric range in excel
6. Extract dates and adjacent value in a range using a date critera in excel
7. How to count unique distinct records in a date range
8. Finding the nearest date in a range of dates using excel formula
9. Create a unique distinct text list from a range containing both numerical and text values in excel
10. Highlight dates within a date range using conditional formatting

Answer:

A range (tbl_text) containing text values

Array formula in B14:

=INDEX(tbl_text, MIN(IF((COUNTIF($B$13:B13, tbl_text)=0)*(COUNTIF(tbl_text, tbl_text)=1), ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(1, COUNTIF($B$13:B13, INDEX(tbl_text, MIN(IF((COUNTIF($B$13:B13, tbl_text)=0)*(COUNTIF(tbl_text, tbl_text)=1), ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1))+COUNTIF(tbl_text, INDEX(tbl_text, MIN(IF((COUNTIF($B$13:B13, tbl_text)=0)*(COUNTIF(tbl_text, tbl_text)=1), ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1) + CTRL + SHIFT + ENTER copied down as far as necessary.

A range (tbl_num) containing numbers

Array formula in D14:

=SMALL(IF(COUNTIF(tbl_num, tbl_num)=1, tbl_num, ""), ROW(1:1)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges

tbl_text (B2:D4)
tbl_num (B7:D9)
What is named ranges?

How to implement array formula to your workbook

Change the named range. If your list starts at, for example, F3. Change $B$13:B13 to F2:$F$2, in the above formula.

Download excel file for this tutorial.

Extract-unique-values-from-a-range-using-array-formulas.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

Related posts:

1. Unique distinct values from multiple columns using array formula
2. Filter text values existing in range 1 but not in range 2 using array formula in excel
3. Filter values existing in range 1 but not in range 2 using array formula in excel
4. Filter common text values in range 1 and in range 2 using array formula in excel
5. Filter unique values from a range using array formula in excel
6. Extract largest values from two columns using array formula in excel
7. Extract duplicates from a range using excel 2007 array formula
8. Extract numbers and text from a range using array formula in excel
9. Extract a unique distinct list from two columns using excel 2007 array formula
10. Extract duplicate text values from a range containing both numerical and text values in excel

Unique cell values are greyed, in the picture above.

Array formula in D14:

=SUM(IF(COUNTIF(tbl, tbl)=1, 1, 0)) + CTRL + SHIFT + ENTER

Unique distinct cell values are greyed, in the picture above.

Array formula in D26:

=SUM(IF(ISBLANK(tbl), 0, 1/(COUNTIF(tbl, tbl)))) + CTRL + SHIFT + ENTER

Unique text cell values are greyed, in the picture above.

Array formula in D38:

=SUM(IF(ISTEXT(tbl)*COUNTIF(tbl, tbl)=1, 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Unique distinct text cell values are greyed, in the picture above.

Array formula in E50:

=SUM(IF(ISTEXT(tbl), 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Unique numerical cell values are greyed, in the picture above.

Array formula in E62:

=SUM(IF(ISNUMBER(tbl)*COUNTIF(tbl, tbl)=1, 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Unique distinct numerical cell values are greyed, in the picture above.

Array formula in E74:

=SUM(IF(ISNUMBER(tbl), 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Named ranges

tbl (B6:F12)
What is named ranges?

How to implement array formula to your workbook

Change the named range.

Download excel file for this tutorial.

Count unique text values in a range containing both numerical and text values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

ISBLANK(value)
Checks whether a reference is to an empty cell and returns TRUE or FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells

Related posts:

1. Filter unique text values from a range containing both numerical and text values in excel
2. Create a unique distinct text list from a range containing both numerical and text values in excel
3. Extract and sort text cells from a range containing both numerical and text values
4. Extract duplicate text values from a range containing both numerical and text values in excel
5. Count unique distinct text values in a range in excel
6. Filter text values existing in range 1 but not in range 2 using array formula in excel
7. Filter common text values in range 1 and in range 2 using array formula in excel
8. Count unique and unique distinct values in a multicolumn range in excel
9. Extract unique values from a range using array formula in excel
10. Filter unique values from a range using array formula in excel

Array formula in B16:

=INDEX(tbl, MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1)), INDEX(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), , 1), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges

tbl (B2:C6)
What is named ranges?

How to implement array formula to your workbook

Change the named range.

Download excel file for this tutorial.

Sort text values alphabetically from a range containing both numerical and text values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE

ROW(reference) returns the rownumber of a reference

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

SMALL(array,k) returns the k-th smallest row number in this data set.

More blog posts on the same topic

• Filter unique text values from a range containing both numerical and text values in excel
• Extract duplicate text values from a range containing both numerical and text values in excel
• Create a unique distinct text list from a range containing both numerical and text values in excel

Related posts:

1. Extract duplicate text values from a range containing both numerical and text values in excel
2. Filter unique text values from a range containing both numerical and text values in excel
3. Create a unique distinct text list from a range containing both numerical and text values in excel
4. Count unique text values in a range containing both numerical and text values
5. Filter text values existing in range 1 but not in range 2 using array formula in excel
6. Filter common text values in range 1 and in range 2 using array formula in excel
7. Count unique distinct text values in a range in excel
8. Extract numbers and text from a range using array formula in excel
9. Extract unique values from a range using array formula in excel
10. Sort text cells alphabetically from two columns using excel array formula