Solved Can you search multiple cells for multiple values?

Microsoft Outlook 2013 32/64-bit (mail m...
November 1, 2018 at 09:39:41
Specs: Windows 64
DerbyDad said I'd be back so here we go!

This is part of a large report and goes along with the last two questions I asked. This item I am looking at a string of cells and (hopefully!) one of the cells will have a code that is part of a list. Instead of checking each cell, I'd like to have one column that shows the 'winner', the first correct code in the list.

I know that probably sounds confusing, more so than it actually is, I am terrible at explanations so let me show you:

A	B	C	D	E	F
TE207	TE220B	TE220B	TE220B	TE220B	TE220B
TE207	TE220B	TE220B	TE220B	TE220B	TE220B
TE207	TE207	TE207	TE220B	TE220B	TE220B
TE206	TE206	TE207	TE207	TE207	TE207
TE220A	TE220B	TE220B	TE220B	TE220B	TE220B
TE206	TE206	TE206	TE206	TE206	TE220A
TE206	TE207	TE207	TE207	TE220B	TE220B

In column G, I'd like to just show the first cell that shows one of these codes: TE207 or TE220A. There are really 8 codes I need to look for and in ten cells, the example is just shortened. I've tried an IF with an ISTEXT and a FIND and a SEARCH but can't figure out how to write a formula that will search all cells on each row and then show whichever code is first.

In row 1-3, the right cell is in column A. In row 4, it's in column C; in row 5, it's in column A; in 5 it's A; in 6 it's F, etc.

I just want one code, even if there are three correct cells in the row, just the first one will do.

Okay, ideas, please!

message edited by GingerLeake


See More: Can you search multiple cells for multiple values?

Reply ↓  Report •

✔ Best Answer
November 1, 2018 at 11:50:34
Here is an improved version where you do not have to combine your data.

With your data like:

     A        B        C       D       E      F       G    H      I
1) TE207    TE220B  TE220B  TE220B  TE220B  TE220B  TE207       TE207
2) TE207    TE220B  TE220B  TE220B  TE220B  TE220B  TE207       TE220A
3) TE207    TE207   TE207   TE220B  TE220B  TE220B  TE207       
4) TE206    TE206   TE207   TE207   TE207   TE207   TE207       
5) TE220A   TE220B  TE220B  TE220B  TE220B  TE220B  TE220A      
6) TE206    TE206   TE206   TE206   TE206   TE220A  TE220A      
7) TE206    TE207   TE207   TE207   TE220B  TE220B  TE207                                             

In column I enter the codes you wish to search for.
Next give your list a Defined Name, I have use the name tlist

Then in cell G1 enter the ARRAY formula:

=IFERROR(INDEX(tlist,MAX(IF(ISERROR(SEARCH(tlist,CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1," ",F1))),-1,1)*(ROW(tlist)-ROW($I$1)+1))),"No Match")

Don't forget to use CTRL-SHIFT-ENTER for the array.

Drag down as many rows as needed and you should get
the same as above.

MIKE

http://www.skeptic.com/



#1
November 1, 2018 at 11:13:13
A macro would work, sure, I just want it to find the first right code. Any of the eight codes is fine, there's no preference which is why I thought putting it in a formula would work.

You know, serach for the first code and if it isn't in 2A thru 2F, then search for the second code in 2A thru 2F. If nothing, the default would be the words "Search necessary."

Oh, I just realized I didn't say this--I want column G to show what code was found. It doesn't matter if it was in position one or 10 but column G should show TE207 (or whatever).

Does that make sense? Search 2a thru 2f for TE207. If nothing, then search 2a thru 2g for TE220A. If nothing search 2a thru 2g for TE201. If nothing, then default to "Search necessary." The position doesn't matter. If for instance, column A says TE207 and column C has TE220A, I don't care if TE220A is shown in G. I just need one code.

I thought by saying 'the first' it would convey there's no preference.


Reply ↓  Report •

#2
November 1, 2018 at 11:33:14
Sorry, I deleted one of my responses. I realized that the solution I offered was not going to work. A macro could be written, but I believe that I have a formula version that should work.

Try this:

1 - Enter your list of 8 codes in a range and name the Range "Codes".

2 - Enter the following formula in G1 using Ctrl-Shift-Enter to create an array formula.

3 - Drag it down as far as you need to.

The formula will search A1 for any of the codes in your list. If #N/A is returned, it will then search B1, then C1, etc. If it finds the code a cell, it will stop and return the code. If A1:F1 all return the #N/A error, then "Search Necessary" will be returned.

=IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,A1)),0)),
IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,B1)),0)),
IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,C1)),0)),
IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,D1)),0)),
IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,E1)),0)),
IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,F1)),0)),
"Search Necessary"))))))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
November 1, 2018 at 11:34:46
Try this:

The SEARCH() function does not work across multiple cells, so to get around this
Concatenate columns A thru F into one long string so your data looks like:

                 A                                B     C     D      
 1) TE207 TE220B TE220B TE220B TE220B TE220B    TE207       TE207
 2) TE207 TE220B TE220B TE220B TE220B TE220B    TE207       TE220A
 3) TE207 TE207 TE207 TE220B TE220B TE220B      TE207       
 4) TE206 TE206 TE207 TE207 TE207 TE207         TE207       
 5) TE220A TE220B TE220B TE220B TE220B TE220B   TE220A      
 6) TE206 TE206 TE206 TE206 TE206 TE220A        TE220A      
 7) TE206 TE207 TE207 TE207 TE220B TE220B       TE207                                    

In column D enter the codes you wish to search for.
Next give your list a Defined Name, I have use the name tlist

Then in cell B1 enter the ARRAY formula:

=IFERROR(INDEX(tlist,MAX(IF(ISERROR(SEARCH(tlist,A1)),-1,1)*(ROW(tlist)-ROW($D$1)+1))),"No Match")

Don't forget to use CTRL-SHIFT-ENTER for the array.

Drag down as many rows as needed and you should get
the same as above.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
November 1, 2018 at 11:50:34
✔ Best Answer
Here is an improved version where you do not have to combine your data.

With your data like:

     A        B        C       D       E      F       G    H      I
1) TE207    TE220B  TE220B  TE220B  TE220B  TE220B  TE207       TE207
2) TE207    TE220B  TE220B  TE220B  TE220B  TE220B  TE207       TE220A
3) TE207    TE207   TE207   TE220B  TE220B  TE220B  TE207       
4) TE206    TE206   TE207   TE207   TE207   TE207   TE207       
5) TE220A   TE220B  TE220B  TE220B  TE220B  TE220B  TE220A      
6) TE206    TE206   TE206   TE206   TE206   TE220A  TE220A      
7) TE206    TE207   TE207   TE207   TE220B  TE220B  TE207                                             

In column I enter the codes you wish to search for.
Next give your list a Defined Name, I have use the name tlist

Then in cell G1 enter the ARRAY formula:

=IFERROR(INDEX(tlist,MAX(IF(ISERROR(SEARCH(tlist,CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1," ",F1))),-1,1)*(ROW(tlist)-ROW($I$1)+1))),"No Match")

Don't forget to use CTRL-SHIFT-ENTER for the array.

Drag down as many rows as needed and you should get
the same as above.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
November 1, 2018 at 12:39:01
Nice formula.

Just tossing out an FYI...

With Google Sheets and Excel for Office 365, the CONCATENATE portion could be replaced with:

TEXTJOIN(" ",1,A1:F1)

It worked with Sheets in Google Docs. I don't have Office 365 to test.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Ask Question