I hope someone can help with this question as I have to believe there is a way or better approach like using an index or array in the formula but I've never done that.
In the spreadsheet below, I am trying to evaluate the following in the formula in column "A".
If column B=Enhancement and if column M contains a color, then the result should be some unique result (don't care what, just so I know to delete it). There can be 7 different colors and I have them in a separate sheet - referenced by "Enhancement LU." Thus, my formula is quite ugly and only works accurately for the first color in the list or results in reference error.
=IF(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$1,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$3,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$2,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$4,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$5,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$6,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$7,"*"),M3:S3,8,FALSE))))))))
Table:
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
In the spreadsheet below, I am trying to evaluate the following in the formula in column "A".
If column B=Enhancement and if column M contains a color, then the result should be some unique result (don't care what, just so I know to delete it). There can be 7 different colors and I have them in a separate sheet - referenced by "Enhancement LU." Thus, my formula is quite ugly and only works accurately for the first color in the list or results in reference error.
=IF(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$1,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$3,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$2,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$4,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$5,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$6,"*"),M3:S3,8,FALSE),OR(B3="Enhancement",VLOOKUP(CONCATENATE("*",'Enhancement LU'!$A$7,"*"),M3:S3,8,FALSE))))))))
Table:
| Only keep #N/A (i.e. if there is a "color" in the title column, delete it. | Site type (Enhancement or Project) | Group | Workstream | Dev | Team | Release | Customer Team | Shared Dev Team | Iteration Path | Dev Tasks | Task Activity | Title | Assigned To | State | Hrs Est | Hrs Remaining | Hrs completed | Team Project | Node Name |
| #N/A | Enhancement | Web | Web | SAP Security | Web and Open Enhancements | Web and Open Enhancements\Recurring | Task | Requirements | Cut Reason DC 2.0 Migration (orange) | John | New | 60 | 60 | 0 | Web | Web | |||
| #REF! | Enhancement | Web | Web | Security | Web | Mobility Project\Iteration 5 | Task | Some mobility task (darkgreen) | Tim | Done | 60 | 44 | 16 | Dev | Dev | ||||
| #N/A | Enhancement | Accounting | Mobility | MM | Security | Mobility Project\Iteration 4 | Task | Meet with EL on User Impact | Mike | Done | 12 | 0 | 12 | JDE | JDE | ||||
| #N/A | Project | CC | Mobility | Dev | Dev | Mobility Project\Iteration 4 | Task | Review User Impact Deck w/ EL (1/06) | Fred | Done | 4 | 0 | 4 | Mobility | Mobility | ||||
| #N/A | Project | MM | Mobility | CC | Security | Mobility Project\Iteration 4 | Task | SAP Sec - Test Corp Roles (yellow) | Pam | Done | 8 | 0 | 8 | Mobility | Mobility |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
