=SI(ESTERREUR(CHERCHE(...));...;SI(ESTERREUR(CHERCHE(...));...;SI(ESTERREUR(CHERCHE(...));...;SI(ESTERREUR(CHERCHE(...));...;SI(ESTERREUR(CHERCHE(...));...;SI(ESTERREUR(CHERCHE(...));...;...))))))
Solved
Bemanace
Posted messages
63
Status
Member
-
Bemanace Posted messages 63 Status Member -
Bemanace Posted messages 63 Status Member -
Bonjour,
I would like to nest 6 "SEARCH" functions (or another function that would work better!) in a single cell.
I would like a function (in P3) that searches for a "WORD"
if "WORD" is in B3, then C3 goes to P3; otherwise, search
if "WORD" is in D3, then E3 goes to P3; otherwise, search
if "WORD" is in F3, then G3 goes to P3; otherwise, search
if "WORD" is in H3, then I3 goes to P3; otherwise, search
if "WORD" is in J3, then K3 goes to P3; otherwise, search
if "WORD" is in L3, then M3 goes to P3; otherwise, search
if "WORD" is in N3, then O3 goes to P3; otherwise, display "- -".
I tried with:
=IF(ISERROR(SEARCH("WORD",$B3)),"- -",$C3)
IF(ISERROR(SEARCH("WORD",$D3)),"- -",$E3)
IF(ISERROR(SEARCH("WORD",$F3)),"- -",$G3)
IF(ISERROR(SEARCH("WORD",$H3)),"- -",$I3)
IF(ISERROR(SEARCH("WORD",$J3)),"- -",$K3)
IF(ISERROR(SEARCH("WORD",$L3)),"- -",$M3)
IF(ISERROR(SEARCH("WORD",$N3)),"- -",$O3)
But Excel doesn't want more than 2; this formula works very well but not more.
=IF(NOT(ISERROR(SEARCH(R$1,$B5))), $C5, IF(NOT(ISERROR(SEARCH(R$1,$D5))),$E5, "- -"))
Do you have another function to suggest?
Thank you very much in advance.
Configuration: Windows 7 / Chrome 40.0.2214.115
I would like to nest 6 "SEARCH" functions (or another function that would work better!) in a single cell.
I would like a function (in P3) that searches for a "WORD"
if "WORD" is in B3, then C3 goes to P3; otherwise, search
if "WORD" is in D3, then E3 goes to P3; otherwise, search
if "WORD" is in F3, then G3 goes to P3; otherwise, search
if "WORD" is in H3, then I3 goes to P3; otherwise, search
if "WORD" is in J3, then K3 goes to P3; otherwise, search
if "WORD" is in L3, then M3 goes to P3; otherwise, search
if "WORD" is in N3, then O3 goes to P3; otherwise, display "- -".
I tried with:
=IF(ISERROR(SEARCH("WORD",$B3)),"- -",$C3)
IF(ISERROR(SEARCH("WORD",$D3)),"- -",$E3)
IF(ISERROR(SEARCH("WORD",$F3)),"- -",$G3)
IF(ISERROR(SEARCH("WORD",$H3)),"- -",$I3)
IF(ISERROR(SEARCH("WORD",$J3)),"- -",$K3)
IF(ISERROR(SEARCH("WORD",$L3)),"- -",$M3)
IF(ISERROR(SEARCH("WORD",$N3)),"- -",$O3)
But Excel doesn't want more than 2; this formula works very well but not more.
=IF(NOT(ISERROR(SEARCH(R$1,$B5))), $C5, IF(NOT(ISERROR(SEARCH(R$1,$D5))),$E5, "- -"))
Do you have another function to suggest?
Thank you very much in advance.
Configuration: Windows 7 / Chrome 40.0.2214.115
1 answer
-
Good evening
Try:
=IFERROR(INDEX(B3:O3;;MATCH("*word*";B3:O3;0)+1);"--")
Best regards
"Imagination is more important than knowledge." A. Einstein-
Sure! Thank you very much for this quick and effective response!
I didn't understand everything in the function, but I applied it blindly, and it works quite well. I was able to apply it to my various searches.
There's one thing left to improve for it to be perfect:
I would like to replace the search for "*word*" with the content of cell P1.
I tried replacing "*word*" with P1, but now it only works when there is exactly the same thing as in P1.
It should check if the word in P1 appears in a search cell.
For example, in P1 I have Wednesday (and only that),
if in B3 I have "Wednesday, Thursday," it should work, but with my tweak, it only works if I have only Wednesday.
What should I put in place of "*WORD*"?
Thank you.- Hello
In the first formula, the * before and after replaces anything, so the function looked for WORD in the middle of a text to do the same with a value in P1; we adjust the formula as follows:
=IFERROR(INDEX(B3:O3;;MATCH("&" & P1 & "&";B3:O3;0)+1);"--")
For explanation: the MATCH function returns the rank of the value being searched in a range; for example, if it finds the value in D3, it returns (3rd position)
The INDEX function returns the value found in a range at the specified position; here the specified position is the one found by MATCH, to which we add 1, so INDEX in this example will return the value in the 4th position, which is that in E3
Finally, the IFERROR function handles the case where no match is found by MATCH, and instead of returning an error message N/A, it returns --
Best regards
-
-
