Insert your formula here. Information in this article applies to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac. (cell contained both D2:D3 then show). This step matches the terms Widgets from column 1 and Titanium from column 2. ;#VALUE!, 3;3, 1}, "", {2;3;4;5;6;7;8;9;10;11;12}-2+1), IF(ISERR({#VALUE!, 1;1, #VALUE! When nesting one function inside another, it is not possible to open the second, or nested, function's formula builder to enter the necessary arguments. Interesting question! =mid(a2, find("-", a2, find("-",a2)+1)+1, find("-",a2, find("-", a2, find("-",a2)+1) +2) - find("-", a2, find("-",a2)+1)-1) Example 4. Follow along to learn how to create custom lookup formulas. ;#VALUE!, #VALUE! Enter the second range into cells D5 to F11. In this tutorial, the COLUMN function is entered as the column index number … To enter multiple arrays, use the ampersand to concatenate the arrays together. To follow the steps in this tutorial, enter the sample data into the following cells, as shown in the image below. Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […] Search columns for a string and return records – AND logic Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […] Enter , (a comma) after the cell reference E3 to complete the entry of the MATCH function's Lookup_value argument. ;11}), {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, Step 4 - Convert Boolean values to corresponding row numbers, IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", ROW(Text_col)-MIN(ROW(Text_col))+1), IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", ROW(A2:A12)-MIN(ROW(A2:A12))+1), IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", {2;3;4;5;6;7;8;9;10;11;12}-MIN({2;3;4;5;6;7;8;9;10;11;12})+1), IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", {2;3;4;5;6;7;8;9;10;11;12}-2+1), IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", {1;2;3;4;5;6;7;8;9;10;11;}), SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1)), SMALL({"";"";"";"";"";"";"";8;"";"";11}, ROW(1:1)). Find text between parentheses ;#VALUE!, 3;3, 1}, "", ROW(A2:A12)-MIN(ROW(A2:A12))+1), IF(ISERR({#VALUE!, 1;1, #VALUE! This step covers adding the Lookup_array argument for the nested MATCH function. ;#VALUE!, 1;1, 2;2, #VALUE! Enter ) (a closing round bracket) to complete the MATCH function. ;#VALUE!, 1;1, 2;2, #VALUE! 1. This cell is where the nested function will be entered. Only copy the formula to your worksheet and create the named ranges. Thanks for bringing this post to my attention. Thank you sir, but if i put the array formula directly in the data validation list.. it only display "BFA" in the list.. i don't want to create a name range for the search result to display in data validation... thanks again in advance. ;2, #VALUE! The Reference Form gives the cell reference or location of the data in the table. Paste image link to your comment. The MATCH function only searches one array for each term specified. The formula isn't finished. The array formula involves nesting the MATCH function inside the INDEX function. The Lookup_array data must be sorted in descending order. SMALL({"";"";"";"";"";"";"";8;"";"";11;""}, 1) and returns 8. ;#VALUE!, 3;3, 1}, "", {1;2;3;4;5;6;7;8;9;10;11}), IF({TRUE, FALSE;FALSE, TRUE;FALSE, TRUE;TRUE, TRUE;TRUE, TRUE;FALSE, TRUE;TRUE, FALSE;FALSE, FALSE;FALSE, TRUE;TRUE, FALSE;FALSE, FALSE}, "", {1;2;3;4;5;6;7;8;9;10;11}), {"", 1;2, "";3, "";"", "";"", "";6, "";"", 7;8, 8;9, "";"", 10;11, 11}, Step 3 - Calculates how often values occur within a range of values. This is the only supplier listed who sells Titanium Widgets. If there had been more than one supplier, the supplier listed first in the database is returned by the function. ;#VALUE!, #VALUE! ;1, #VALUE! ;1, #VALUE! You'll complete the formula in the instructions below. Change the cell reference for the named range Search_col. SMALL(IF(FREQUENCY(IF(ISERR(SEARCH(TRANSPOSE(Search_col), Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(Text_col)-MIN(ROW(Text_col))+1)=ROWS(Search_col), ROW(Text_col)-MIN(ROW(Text_col))+1, ""), ROW(1:1)), SMALL({"";"";"";"";"";"";"";8;"";"";11;""}, ROW(1:1)).