Thanks for taking the time to read this long post. Several months ago I posted a problem regarding searching four columns in order to return a specific text string. There were several great responses and one in particular that worked very well, however I have not been able to successfully alter the formula to search additional columns (up to 10 columns). I really have tried to understand each of the nine different nested functions within the two different formulas, but I keep getting an error message after every attempt to add any additional columns. I have posted the original spreadsheet that contains the two formulas in the "helper columns" I have also added two additional columns of data to be sorted if someone feels adventurous enough to give it a try by altering the two formulas. The formula posted below really is brilliant in its execution. It works flawlessly when extracting user defined text and numbers from four columns, and anyone who is trying to extract specific data from a table will find it a very useful tool. (Special thanks to user WHER for your help).
I have posted the location of the original thread and the two formulas below used to sort the four columns. The crux of the original problem was that there were two different types of data being searched in the four columns. Both types are color coded black and red. The first formula in "Helper Column K" searches for one type of data , while the second formula in "Helper Column L" searches all four columns if the specific type of data is not found in columns "B" and "D" by the first formula. In the formulas shown below, columns $B$2:$B$18 and $D$2:$D$18 in the first formula are being searched to find the specific text shown in cell "I18". In the attached example, I want to add columns "F" and "G" to be searched as well. Any help is greatly appreciated, and let me give a special thanks to the people on this forum. Although I am new to Excel, my skills have improved dramatically these past months, mainly by reading the posts on this forum whenever I encounter a problem.
=IFERROR(SUBSTITUTE(IFERROR(INDEX($B$2:$B$18,MAX(IF(ISNUMBER(FIND(I18,$B$2:$B$18))*(LEN(I18)<LEN($B$2:$B$18))=1,ROW($B$2:$B$18),""))-1),INDEX($D$2:$D$18,MAX(IF(ISNUMBER(FIND(I18,$D$2:$D$18))*(LEN(I18)<LEN($D$2:$D$18))=1,ROW($D$2:$D$18),""))-1)),I18,""),"")
=IFERROR(IFERROR(INDEX($C$2:$C$18,MATCH(I18,$B$2:$B$18,0)),INDEX($E$2:$E$18,MATCH(I18,$D$2:$D$18,0))),"")
My original post and question is provided below.
http://www.excelforum.com/excel-gene...o-a-table.html
Bookmarks