# Partial match with two conditions and return multiple results

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria.

The formula returns all rows that contain the criteria, the first condition is evaluated to column B and the second condition column C. Both cells must contain the strings respectively on the same row.

### Table of Contents

**Question:**

How do I search a list containing *First name* column and* a last name* column? I want to search both columns at the same time with two different criteria (one criteria for each column) and then display multiple search results.

## 1. Partial match with two conditions and return multiple search results

I created two search fields. First and last name in F2 and F3. The search results are presented in columns D and E. See the picture below.

The array formula in cell E8:

### 1.1 How to create an array formula

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

Recommended articles

Array formulas allows you to do advanced calculations not possible with regular formulas.

Copy cell D6 and paste it to cells below and to the right as far as needed.

**1.2 Explaining the array formula in cell D6**

#### Step 1 - Find the first partial match

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

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

SEARCH($F$2, $C$3:$C$17)

becomes

SEARCH("e",{" Roger "; " Novak "; " Andy "; " Nikolay "; " Andy "; " Juan Martin "; " Fernando "; " Fernando "; " Stanislas "; " James "; " David "; " Tommy "; " Marin "; " Radek "; " Nicolas "})

and returns

{5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 3; 3; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!}.

#### Step 2 - Second partial match

SEARCH($F$3, $B$3:$B$17)

becomes

SEARCH("o",{"Federer";"Djokovic";"Murray";"Davydenko";"Roddick";"Del Potro";"Verdasco";"Gonzalez";"Wawrinka";"Blake";"Nalbandian";"Robredo";"Cilic";"Stepanek";"Almagro"})

and returns

{#VALUE!; 3; #VALUE!; 9; 2; 6; 8; 2; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!; 7}.

#### Step 3 - Multiply arrays

The asterisk character lets you multiply the arrays creating AND logic meaning both values in the same position must be a number. This will only match rows where both conditons are met.

(SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))

becomes

{5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 3; 3; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!}*{#VALUE!; 3; #VALUE!; 9; 2; 6; 8; 2; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!; 7}

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 24; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}.

#### Step 4 - Check if the value is* a* number

The array calculated in the previous step has error values that we must take care of. The ISNUMBER function returns TRUE if a value in the array is a number and FALSE for everything else including error values.

ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17)))

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 24; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!})

and returns

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

#### Step 5 - Replace boolean values with corresponding row numbers

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

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

IF(ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))), MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), "")

becomes

IF({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), "")

The ROW function lets you create numbers representing the rows based on a cell range.

ROW($B$3:$B$17) returns {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}.

The MATCH function finds the relative position of a given string in an array or cell range. This will create an array from 1 to n where n is the number of rows in cell range $B$3:$B$17.

MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17))

becomes

MATCH({3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17})

and returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}.

IF({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), "")

becomes

IF({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}, "")

and returns {""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; ""}.

#### Step 6 - Extract k-th smallest row number

The SMALL function returns the k-th smallest value from a group of numbers.

SMALL(*array*, *k*)

SMALL(IF(ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))), MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), ""), ROWS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; ""}, ROWS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; ""}, 1)

and returns 7.

#### Step 7 - Get value from B3:C17 based on row and column number

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX($B$3:$C$17, SMALL(IF(ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))), MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

INDEX($B$3:$C$17, 7, ROWS($A$1:A1)), COLUMNS($A$1:A1))

The COLUMNS function returns a new value in each cell, this makes sure that the same value is not returned twice.

INDEX($B$3:$C$17, 7, COLUMNS($A$1:A1))

becomes

INDEX($B$3:$C$17, 7, 1)

returns ""Verdasco" in cell E8.

## 2. Partial match with two conditions and return multiple search results - Excel 365

The image above shows a dynamic array formula that is much shorter than the formula in section 1 for previous Excel versions.

Excel 365 dynamic array formula in cell E8:

### Explaining formula in cell E8

#### Step 1 - Partial match first condition

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

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

SEARCH($F$2, $C$3:$C$17)

becomes

SEARCH("e",{" Rog**e**r "; " Novak "; " Andy "; " Nikolay "; " Andy "; " Juan Martin "; " F**e**rnando "; " F**e**rnando "; " Stanislas "; " Jam**e**s "; " David "; " Tommy "; " Marin "; " Rad**e**k "; " Nicolas "})

and returns

{**5**; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; **3**; **3**; #VALUE!; **5**; #VALUE!; #VALUE!; #VALUE!; **5**; #VALUE!}.

#### Step 2 - Partial match second condition

SEARCH($F$3, $B$3:$B$17)

becomes

SEARCH("**o**",{"Federer";"Dj**o**kovic";"Murray";"Davydenk**o**";"R**o**ddick";"Del P**o**tro";"Verdasc**o**";"G**o**nzalez";"Wawrinka";"Blake";"Nalbandian";"R**o**bredo";"Cilic";"Stepanek";"Almagr**o**"})

and returns

{#VALUE!; 3; #VALUE!; 9; 2; 6; 8; 2; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!; 7}.

#### Step 3 - AND logic

The asterisk character lets you multiply the arrays creating AND logic meaning both values in the same position must be a number. This will only match rows where both conditons are met.

SEARCH($F$2, $C$3:$C$17)*SEARCH($F$3, $B$3:$B$17)

becomes

{5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 3; 3; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!}*{#VALUE!; 3; #VALUE!; 9; 2; 6; 8; 2; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!; 7}

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 24; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}.

#### Step 4 - Check if number

The array calculated in the previous step has error values that we must take care of. The ISNUMBER function returns TRUE if a value in the array is a number and FALSE for everything else including error values.

ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17)))

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 24; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!})

and returns

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

#### Step 5 - Extract records

The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category and is only available to Excel 365 subscribers.

FILTER(array, include, [if_empty])

FILTER($B$3:$C$17, ISNUMBER(SEARCH($F$2, $C$3:$C$17)*SEARCH($F$3, $B$3:$B$17)))

becomes

FILTER($B$3:$C$17, {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns

{"Verdasco", " Fernando "; "Gonzalez", " Fernando "}.

## 3. Get Excel file

### Search and return multiple values category

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]

Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 47 Responses to “Partial match with two conditions and return multiple results”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

How would I go about looking up data in an cross ref table.

I have the header row (i.e. 24) value and the column (mm) value and want to return the x/y value. i.e I have 25/X and 9/Y item and want 1.8 to be returned.

(mm) 22 23 24 25 26 27 28 29

8 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3

9 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3

10 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3

11 2.2 2.8 2.8 2.8 2.8 2.8 3.3 3.3

thanks

See this post: https://www.get-digital-help.com/looking-up-data-in-a-cross-reference-table-in-excel/

Could this approach be expanded for more than 2 columns?

And if the values were numbers is there a way to display the values within a range between the values in 2 cells?

I don´t understand.

You want to search a range bigger than 2 columns?

If two numbers (or numbers between) match on any column on the same row, it is a match?

My comment is two seperate issues.

The first is if I can expand this equation set into more than two colums of data, say if I had a first, middle and last name column could I only display the values in which all three cases are true?

The second question is whether the equation can be adjusted to search and display a range of values, if the values were numerical, say 1 - 40 could I set a range of 20 - 25 (either in one or 2 cells) and get 6 values (inclusive)?

Apologies if it is hard to understand

Gavin, your first question.

See this blog post: https://www.get-digital-help.com/lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-3/

Gavin, your second question:

https://www.get-digital-help.com/search-and-display-a-range-of-values-in-excel/

I have a column "A" with a last name.. I have another columb with a date in it "E"... I need to be able to list the names from columb A on a second sheet whos columb e date has past....

thanks

todd B, see this post: https://www.get-digital-help.com/list-names-whos-date-has-past-in-excel/

Well, I have been looking long for this wonderful idea, and previously I had to result in two step utilizing rank command etc etc..

Thank you for the work, and thenk the NET in general!

Back to what I had in mind, I wanted specificaly do this

assume a column b from 2 to 100, where the word "check" occures, there is dropdown list resticted option, among "cash" and "other".

next to it, there is a column c from 2 to 100 also with dates, Now all I want is to incorporate in that wonderful formula of yours for simple sorting, the condition to have value "check" on col b every time I include a date for to sort..and of course to include the option to have the cells blank when there is no value...after sorting..?

Thank you for your time, it has been so much fun doing stuff in excel!!!

am I asking for too much

also, while this formula modified for one constant filtration, either from small or family names lets say the "o" s, it does not alphabetize the results.

so what I am looking for is your classic short formula, single column, the one you have with pairs of letters...ee, wr, etc, but with a filter to screen the results too...

Is it possible to lets say pick the cell that is next to it horizontaly, and the cell next to it too, like the vlookup can do?

Ultimately I want to filtrate and indexize the dates, but also bring in the rest of the row, the name the ammount etc, associated with the date...

thank you once again

Can you upload an excel example file?

https://www.get-digital-help.com/contact/

Great solution. I was wondering, though, how you seemed to "group" the cells D6:D20 and E6:E20. I got the Excel example and played around and noticed that changing something in D6 automatically changed every cell from D6 to D20. That's cool!

Feel free to e-mail me if you prefer. Thanks!

Art,

I use search() to find rows that match. INDEX() returns a value in a given range.

The formula in D6:D20:

= INDEX($B$2:$B$16, ...

The formula in E6:E20:

= INDEX($A$2:$A$16, ...

Thanks for commenting!

/Oscar

Is is possible to use Index/Match using multiple criteria as you listed above except display all of the search results in a list (data validation) so that I can select one of the results from the list?

This is amazing! I've been looking for a way to filter multiple outputs using excel code. Now to dissect the formula...

KW,

Thank you for your comment!

Hi, I need help with the multiple criteria as well. But it also involved in min/max value.

The Data as per shown below:

Value in USD

Weight in Kg Zone Zone Zone Zone Zone

Min Max A B C D E

2.50 25.0 7.95 8.79 9.93 10.50 8.23

25.50 50.0 7.66 8.50 9.64 10.21 7.94

50.50 100.0 6.24 7.08 8.22 8.79 6.52

100.50 500.0 5.12 5.96 7.10 7.67 5.40

I would like to lookup for the Price (the value that I need it to show up automatically) based on the specified Zone and the weight.

Example:

Zone Weight Price

E 24.00 8.23

A 30.00 7.66

C 50.50 8.22

Thanks in advance for your great help.

Liz,

Dear Oscar,

Thank you very much.

Deeply sorry for my really rate reply :")

Hi please help on the multiple criteria. I need as below:

Criteria:

1. Lookup the material code specified on column A to column C

2. Column D must not be empty

Once the above 2 conditions are satisfied, the result must display the latest date from column E

Reference:

A B C D E

1 Material code Date Material code Invoice ref Date

2 03405140F0 03405140F0 1800102639 7/2/2012

3 03405341F0 03405140F0 1800102639 7/2/2012

4 03406210F0 03405140F0 1800103010 7/6/2012

5 03405341F0 1800103010 7/2/2012

6 03405341F0 7/28/2012

7 03405341F0 1800101179 7/24/2012

8 03405341F0 8/24/2012

9 03405140F0 1800099691 7/26/2012

10 03405140F0 8/26/2012

11 03406210F0 1800099691 8/2/2012

Result to display must be:

B2 - 7/26/2012

B3 - 7/24/2012

B4 - 8/2/2012

Hi please help on the multiple criteria. Below are the criterias:

1. Lookup the material code specified on column A to column C

2. Column D must not be empty

Once the above 2 conditions are satisfied, the result must display the latest date from column E

Reference:

Column A

1 Material code

2 03405140F0

3 03405341F0

4 03406210F0

Column C

1 Material code

2 03405140F0

3 03405140F0

4 03405140F0

5 03405341F0

6 03405341F0

7 03405341F0

8 03405341F0

9 03405140F0

10 03405140F0

11 03406210F0

Column D

1 Invoice reference

2 1800102639

3 1800102639

4 1800103010

5 1800103010

6

7 1800101179

8

9 1800099691

10

11 1800099691

Column E

1 Date

2 7/2/2012

3 7/2/2012

4 7/6/2012

5 7/2/2012

6 7/28/2012

7 7/24/2012

8 8/24/2012

9 7/26/2012

10 8/26/2012

11 8/2/2012

Result to display in column B must be:

B2 - 7/26/2012 (from E9)

B3 - 7/24/2012 (from E8)

B4 - 8/2/2012 (from E11)

Thanks in advance

Michael,

Array formula in cell B2:

=MAX((A2=$D$2:$D$11)*$G$2:$G$11*($E$2:$E$11<>""))

How to create an array formula

1. Select cell B2

2. Paste formula in formula bar

3. Press and hold Ctrl + Shift

4. Press Enter

Copy cell B2 and paste to cell B3 and B4.

I have been trying to use this article to lookup data against 2 sets of criteria and return multiple results. However my results to be returned are not the same as my search cells i.e. I want to search on multiple criteria and if found bring back a different column in the row, but there will be multiple results that I need to display.

Can anyone help? It would be much appreciated

Thanks

rachel,

Array formula in cell F8:

=INDEX($D$3:$D$17, SMALL(IF(ISNUMBER((SEARCH($G$3, $C$3:$C$17))*(SEARCH($I$3, $B$3:$B$17))), ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1, ""), ROW(A1)))

I was using your code to track who applied for vaction days and take from a list and put in calender format. It worked great until I went to double digit numbers. It is picking up the 1 from 12 and putting the persons name on the 1, 2, and 12 of the month. Could you please help me?

Thanks

Dee

Deanna,

I don´t know how your spreadsheet looks like but I think you can make use of this formula:

=INDEX(cell_ref,MATCH(lookup_value,cell_ref,0))

Thanks for all your help you are great! It is hard to explain and you seem to get it with ease. :)

HAve a great day you are the best!!

Hello,

I have a question. I've used search, rank and vlookup and order to have a cell that I can used like a search engine trough my spreadsheet. The thing is that I can use it only to show one column with results of the searching process and I can't show more then one results. For example: a table from H5: k1000, ( in the column K I have some numbers) and in A B colmuns I've made the searching cell(engine) and the results are dependent of what I choose ( in one cell with data validation). The big problem is the results showed are not correlated with K column.

sorry... can somebody help me, please??

thank you

Andra,

read this:

Return multiple records

Hi! Thanks for the detailed nature of your explanations. I have found them so educational.

Do you know of a way to include certain criteria while excluding others? In your example above, could you have your formula search for the criteria given in $G$3 and $I$3, while excluding (hypothetical) criteria in $K$3 and $M$3? For example, say you wanted to include last names with the letter "o", and first names with the letter "e", while excluding any last names containing the letter "v" and excluding last names with the letter "q"? (of course you don't have any last names with the letter "q" in it, but just for the purpose of illustrating how to exclude multiple criteria...)

Does that makes sense?

Eager to hear any insights you might have. Thanks!

Carma,

Array formula in cell E10:

Array formula in cell F10:

Get the Excel filemultiple-criteria-lookup-with-multiple-results.xlsx

Hi, sorry for digging up an old post but I'd really appreciate your help on this.

I'm trying to search a range of cells for a range of values. Say I wanna search B1:B50 for values in D1:D20(in my case there's multiple matches, i.e. D3 appears multiple times in B1:B50).

For every match, I would like to input a corresponding value from column X to column C. (Back to my example: Say D3 matches B2,B3,B5,B7; so i need to map X3 to C2,C3,C5,C7).

Is this doable in cell/array formula? Or must I resort to macro? Anyways I hope I'm making sense here. Thanks!!

Ethan,

I think this post answers your question:

https://www.get-digital-help.com/search-for-multiple-text-strings-in-multiple-cells-in-excel-part-2/

Hello again, I managed to solve it with vlookup. But thanks so much for your help =)

Hi, is there any way I can use vlookup to automatically post figure from journal voucher to general ledger? pls help me

Pls I need your help on how to automatically post figure from journal to general ledger using excel

Mufliu,

Can you explain in greater detail?

Hi, gud evening! I am having a trouble using excel formulas. I am doing accounting in excel and i don't know the formula to use.I want those journal entries to be posted automatically in the ledger.thank u

Dear Oscar. I'm sorry to reply on such old post but I was studying this scenario and the formulas used on it and thought it's a great solution. I have almost the same need. The only difference is that the result should be a list with only one instance of each record. For instance: I got the sheet and inserted a row with a new entry for Fernando Gonzales and as I did it a new instance for the same name has appeared on the list. I needed it to appear there only once for each name. Just to explain my real scenario: I have 3 columns. DESCRIPTION - CATEGORY - VALUE. On the DESCRIPTION Column there can be a lot of repeated values ( Like ROBERT, JENNIFER, ROBERT, and so on ). On the category there can be a lot of values ( Like SALES, TRADE, RETURNS, MAINTENANCE, and so on ). The column Value contains the value for the operation. I need to have a separate sheet for each category and for each category I need to have the DESCRIPTION and TOTAL VALUE ( which is the sum of all the values for that description on that category ). For instance: On the main sheet I have 4 entries with the same name ( let's say JOHN ) on the DESCRIPTION Column. The category for these 4 entries is SALES and each VALUE for them is 100.00. So, I need to have a Sheet Called "SALES" ( which will be created previously ) and this sheet needs to have the following result: Only one entry with the DESCRIPTION "JOHN" and the TOTAL VALUE as "400.00" ( the sum of all the other 4 entries on the main sheet ). I wanted to do this without Pivot Tables and VBA. Is it possible? Thank you!

Andre,

Array formula in cell A10:

=INDEX($A$2:$A$7,MATCH(0,COUNTIF($A$9:A9,$A$2:$A$7),0))

Read more:

How to extract a unique distinct list from a column

Formula in cell B10:

=SUMIF($A$2:$A$7,A10,$C$2:$C$7)

Get the Excel file

Andre.xlsx

Oscar. Thank you for your help! I will apply this solution to my scenario! You are great! Cheers!

Hello

Hi Oscar,

I've two different sheets in which only one thing is unique i.e Name of the product(Title). So basically i need to find out the sku code which is linked with the product. I need that SKU code in single line break with comma. Please suggest me some formula

i have read your read your site and you come closer to what i am trying to do than anyone on the web- hopefully you will help me i work for a lawyer that wants all client payments on a spreadsheet - she wants a search box to pull up all rows that have the last name and display the results between the search box and the starting row of the spreadsheet - i cant get it to return the results at all please help see my example

{ name here } button ( optional)

want the results to populate here

''''''''''''''

''''''''''''''' 12 lines (no one ever pays more than once a month )

''''''''''''''

''''''''''''

A B C D E

1 date first name last name amount paid payment type

2 1/1/16 bob green 100 cc

3 1/3/17 andy red 50 cash

4 1/5/17 steve white 100 cash

5 1/8/16 tom white 100 cash

6 1/12/17 bob green 3000.00 cc

so when i search for green i will get all the greens that have paid through out the entire worksheet which has 719 rows and 5 columns i want the result to show in the area above the list and below the search box

Hi

my data single cell 12 values like this in below data i want to find out each cell min/max and average for this please give me formula for this thanks

3.78,3.79,3.77,3.78,3.78,3.77,3.78,3.79,3.79,3.79,3.79,3.78

3.51,3.51,3.51,3.50,3.51,3.51,3.52,3.51,3.51,3.51,3.51,3.51

3.61,3.61,3.61,3.61,3.61,3.61,3.60,3.61,3.61,3.59,3.61,3.61