# How to find a text in a group of columns and return the matching column headers.

#### Ramashesh

##### New Member
Hi,

I need to know if there is a way to search for a text in a group of columns and return the respective column headers. Also would need a custom text while returning the value.

I have a spreadsheet of 6000 records which gives me the details of the states to which it pertains, so i need a formula to know if the record is applicable to a state and if yes, then display the column header "which has the state code". Here is an example.

 Record Name WA OR NV ID Expected Result Record 1 X X X X All Record 2 X N/A N/A X All - Except OR, NV Record 3 N/A N/A N/A X ID Only Record 4 N/A X X X All - Except WA

<tbody>
</tbody>

So in the above example,
Record 1 - if "X" is present in all the 4 states, then i need to display "All" in the result.
Record 2 - if "N/A" is present in 2 states, then in the result column i need to display custom text "All - Except" and then pull the column headers which has "N/A" (so that i get "All - Except OR, NV")
Record 3 - if "N/A" is present in 3 states and "X" is present in only in one state i need to display the column header of the state which has "X" and also a custom text which says "Only" ( so that i can display "ID Only")
Record 4 - > if "X" is present in 3 states and "N/A" is present in one state then i need to display custom text "All - Except" and then pull the column headers which has "N/A" (so i need to display"All - Except WA")

Can we build a formula which can use be used to get the desired results as mentioned above? I used the Index and match combination, hlookup, vlookup, "If and If error combination" and noting seems to be working.

Your help in this regard would be highly appreciated.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi Ramashesh
Welcome to the board

Please post which excel version you are using so that we know what's available to you.

Last edited:
Hi Pgc01,

I am using excel 2013 version.

Unfortunately with excel 2013 you still don't have functions to concatenate arrays and so you can't have a general formula for your problem

If you have always just a very small number of states you could try a formula that will try all the values one by one, not pretty

For a general solution I see several options, all with vba. Here are 2 extreme ones, all vba or minimum vba:

1 - you write a vba solution that reads the table and does all the processing needed and writes the results

2 - you just write a small UDF that concatenates an array and write a normal formula.

This is an example for option 2.

In F2:

=IF(COUNTIF(B2:E2,"X")=COLUMNS(B2:E2),"All",IF(COUNTIF(B2:E2,"X")=1,INDEX(\$B\$1:\$E\$1,MATCH("X",B2:E2,0))&" Only","All - Except "&ConcatArray(", ",IF(B2:E2<>"X",\$B\$1:\$E\$1,""))))

This in an array formula, you have to confirm it with CTRL-SHIFT-ENTER

Copy down

For the ConcatArray() I used:

Code:
``````' Concats the elements of an array, ignores blanks
Function ConcatArray(sDelimiter As String, vArray As Variant) As String
Dim v As Variant
Dim s As String

For Each v In vArray
If v <> "" Then s = s & sDelimiter & v
Next v
ConcatArray = Mid(s, Len(sDelimiter))
End Function``````

Last edited:
In the cell that says "All" (F2) in your example, put:
=IF(COUNTIF(B2:E2,"X")=4,"All ",IF(COUNTIF(B2:E2,"X")=1,IF(B2="X",B\$1,"")&IF(C2="X",C\$1,"")&IF(D2="X",D\$1,"")&IF(E2="X",E\$1,"")&" Only ","All - Except "&IF(B2="X","",B\$1&",")&IF(C2="X","",C\$1&",")&IF(D2="X","",D\$1&",")&IF(E2="X","",E\$1)))

And to the right of that cell, put:
=LEFT(H2,LEN(H2)-1)

And hide the column to the left. You can actually combine both formulas, but it's long. In F2:
=LEFT(IF(COUNTIF(B2:E2,"X")=4,"All ",IF(COUNTIF(B2:E2,"X")=1,IF(B2="X",B\$1,"")&IF(C2="X",C\$1,"")&IF(D2="X",D\$1,"")&IF(E2="X",E\$1,"")&" Only ","All - Except "&IF(B2="X","",B\$1&",")&IF(C2="X","",C\$1&",")&IF(D2="X","",D\$1&",")&IF(E2="X","",E\$1))),LEN(IF(COUNTIF(B2:E2,"X")=4,"All ",IF(COUNTIF(B2:E2,"X")=1,IF(B2="X",B\$1,"")&IF(C2="X",C\$1,"")&IF(D2="X",D\$1,"")&IF(E2="X",E\$1,"")&" Only ","All - Except "&IF(B2="X","",B\$1&",")&IF(C2="X","",C\$1&",")&IF(D2="X","",D\$1&",")&IF(E2="X","",E\$1))))-1)

This should cover all the criteria:

=IF(COUNTIFS(B2:E2,"x")=4,"ALL",IF(COUNTIFS(B2:E2,"N/A")=3,LOOKUP("x",B2:E2,\$B\$1:\$E\$1)&" only",IF(COUNTIFS(B2:E2,"N/A")=1,"All - Except "&LOOKUP("N/A",B2:E2,\$B\$1:\$E\$1),"All - Except "&IF(B2="N/A",B\$1&" ","")&IF(C2="N/A",C\$1&" ","")&IF(D2="N/A",D\$1&" ","")&IF(E2="N/A",E\$1,""))))

Hi Vogel,

Thanks for your formula it is working, however this works for the static data but in my case the states might increase and i might have all the 54 states, so i guess the permutations in the formula provided by you needs to be re-written every time. Is my understanding correct?

I there any way i can make it dynamic just by increasing the range of the column when a new state is added?

Hi PCG,

Looks like the second option would work fine for me, but how do i create the UDF. If i copy your code in VBA editor it is asking for a macro name.

Hi PCG,

I was able to create a UDF successfully.

Thanks a ton for your help. The formula with UDF combination worked for my problem statement.

I have tested the second option for 10 states and it worked fine, will this formula extend to as many states as possible?

I appreciate your help in this regard again.

Hi Vogel,

Thanks for your formula it is working, however this works for the static data but in my case the states might increase and i might have all the 54 states, so i guess the permutations in the formula provided by you needs to be re-written every time. Is my understanding correct?

I there any way i can make it dynamic just by increasing the range of the column when a new state is added?

This will accommodate more columns besides the Multiple states listing, small adjustment for additional state necessary.

=IF(COUNTIFS(B2:E2,"x")=COLUMNS(B2:E2),"ALL",IF(COUNTIFS(B2:E2,"N/A")=COLUMNS(B2:E2)-1,LOOKUP("x",B2:E2,\$B\$1:\$E\$1)&" only",IF(COUNTIFS(B2:E2,"N/A")=1,"All - Except "&INDEX(\$B\$1:\$E\$1,,MATCH("N/A",B2:E2,0)),"All - Except "&IF(B2="N/A",B\$1&" ","")&IF(C2="N/A",C\$1&" ","")&IF(D2="N/A",D\$1&" ","")&IF(E2="N/A",E\$1,""))))

Replies
5
Views
334
Replies
1
Views
203
Replies
5
Views
127
Replies
5
Views
371
Replies
1
Views
118

### Forum statistics

1,196,512
Messages
6,015,629
Members
441,912
Latest member
Rayna_rahman00 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back