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.
<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.
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.