1. ## Advance searching with cells

Hi All,

I'm relatively new to this but this problem really has me stumped. I'll try and describe it best I can and any help would be greatly appreciated.

Search a large data range in a worksheet and if any cell matches a specific combination of upper and lower letters, then output the contents of the cell adjacent to each. The combination of letters will always be 3 upper case letters (~24 combinations) followed by one or more lower case letters in any combination (a to t). Currently, Im using the following formulas as arrays to extract the row numbers for each matching cell found, followed by the associated column numbers.

In this example, the first three upper case letters to search are in cell A1 and the lower case letters are in B1 which have been "ANDed" together in the formulas below, and the data to search is in the range A2 to W100.

To get the rows that match:
{=SMALL(IF(\$A\$2:\$W\$100=\$A\$1&\$B\$1,ROW(\$A\$2:\$W\$100)),ROWS(\$BA\$2:BA2))}

To get the columns:
{=SMALL(IF(\$A\$2:\$W\$100=\$A\$1&\$B\$1,IF(ROW(\$A\$2:\$W\$100)=BB2,COLUMN(\$A\$2:\$W\$100))),COUNTIF(\$BB\$4:BB4,BB4))}

and then using an indirect function with an offset copied down to output the results.

This works ok but I need \$A\$1&\$B\$1 in the above to be variable where it will look for 3 upper case letter and ANY combination of lower case letter.

I.e. some cells contain: PJMa, PJMab, PJMbc, SIEa, SIEb, SIEabc, PJMac, PJMb. So searching for "PJM" and "a" will find PJMa, PJMab, and PJMac. Hope that make sense..

Thanks  Register To Reply

2. ## Re: Advance searching with cells

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.  Register To Reply

3. ## Re: Advance searching with cells

Ok thanks, sample workbook attached.  Register To Reply

4. ## Re: Advance searching with cells

Complex formula (based on function 4)

=IFERROR(INDEX(\$I\$6:\$I\$11,SMALL(IF((LEFT(\$H\$6:\$H\$11,3)=\$E\$34)*((ISNUMBER(SEARCH(\$M\$6,\$H\$6:\$H\$11)))+(ISNUMBER(SEARCH(\$M\$7,\$H\$6:\$H\$11)))+(ISNUMBER(SEARCH(\$M\$9,\$H\$6:\$H\$11)))),ROW(\$B\$6:\$B\$11)-ROW(\$B\$6)+1,""),ROWS(\$B\$6:\$B6))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.  Register To Reply

5. ## Re: Advance searching with cells

Many thanks, it looks like its almost doing what I want but my poor description hasn't helped.

In your amended sheet, cell G18 contains:

=IFERROR(INDEX(\$C\$6:\$C\$11,SMALL(IF((LEFT(\$B\$6:\$B\$11,3)=\$E\$18)*(ISNUMBER(SEARCH(\$E\$19,\$B\$6:\$B\$11))),ROW(\$B\$6:\$B\$11)-ROW(\$B\$6)+1,""),ROWS(\$B\$6:\$B6))),"")

I'd like this to search the whole data range based on inputs from E18 and E19 so I've changed it to this...

=IFERROR(INDEX(\$B\$6:\$I\$11,SMALL(IF((LEFT(\$B\$6:\$I\$11,3)=\$E\$18)*(ISNUMBER(SEARCH(\$E\$19,\$B\$6:\$I\$11))),ROW(\$B\$6:\$B\$11)-ROW(\$B\$6)+1,""),ROWS(\$B\$6:\$B6))),"")

But that doesn't seem to work?  Register To Reply

6. ## Re: Advance searching with cells

It won't work unless the column (as well as row) is defined.

I have no idea why the data is organised as it is but it would much better with just two columns only (code/document).  Register To Reply

7. ## Re: Advance searching with cells

Added sample in Sheet2: just change D2 for example.  Register To Reply