Johnstone200
New Member
- Joined
- Apr 12, 2022
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
Hello,
I am running a report and searching for key words in the spreadsheet that i recieve. I am searching for text within 3 colums (B, M and N) and i want to return the value in Z. I use an OR formula for W and an AND for X as i search for different text combinations. The formula used is as per below (the OR one).
However, for each formula i get the result in W and X for the number of times the word is seen - "no image" in this case. How can i run the formula to only show the result once. For example in this situation i only want to see LANE 2, and not repeated 3 times.
=IF(OR(ISNUMBER(SEARCH($AB$1,B2)),ISNUMBER(SEARCH($AC$1,B2)),ISNUMBER(SEARCH($AD$1,B2))),Z2,"")&IF(OR(ISNUMBER(SEARCH($AB$1,M2)),ISNUMBER(SEARCH($AC$1,M2)),ISNUMBER(SEARCH($AD$1,M2))),Z2,"")&IF(OR(ISNUMBER(SEARCH($AB$1,N2)),ISNUMBER(SEARCH($AC$1,N2)),ISNUMBER(SEARCH($AD$1,N2))),Z2,"")
Or is there a better way to write the formula?
Thanks
I am running a report and searching for key words in the spreadsheet that i recieve. I am searching for text within 3 colums (B, M and N) and i want to return the value in Z. I use an OR formula for W and an AND for X as i search for different text combinations. The formula used is as per below (the OR one).
However, for each formula i get the result in W and X for the number of times the word is seen - "no image" in this case. How can i run the formula to only show the result once. For example in this situation i only want to see LANE 2, and not repeated 3 times.
=IF(OR(ISNUMBER(SEARCH($AB$1,B2)),ISNUMBER(SEARCH($AC$1,B2)),ISNUMBER(SEARCH($AD$1,B2))),Z2,"")&IF(OR(ISNUMBER(SEARCH($AB$1,M2)),ISNUMBER(SEARCH($AC$1,M2)),ISNUMBER(SEARCH($AD$1,M2))),Z2,"")&IF(OR(ISNUMBER(SEARCH($AB$1,N2)),ISNUMBER(SEARCH($AC$1,N2)),ISNUMBER(SEARCH($AD$1,N2))),Z2,"")
Or is there a better way to write the formula?
B | M | N | W | X | Z | no image | no image | no image |
TRAYS COMING UP WITH NO IMAGE | no image | Trays showing no image. | LANE 2LANE 2LANE 2 | LANE 2LANE 2LANE 2 | LANE 2 |
Thanks