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",{" 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 - Partial match second condition
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 - 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 […]
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.
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 file
multiple-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