Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie: Ela 22 Cluj pe Simpatie.ro
| Femeie 22 ani Cluj cauta Barbat 22 - 48 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
Source:
Generic formula =COUNTIF(range;value)>0
I have a named range called HOLIDAYS with a list of official holidays and I want to check if a date falls in that range: =COUNTIF(HOLIDAYS;C5)>0
This formula will return true or false.
_______________________________________
|
|
pus acum 5 ani |
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Source:
=COUNTIF(range;"*"&value&"*")>0
or
=COUNTIF(range;"*"&D5&"*")>0
These display TRUE/FALSE. If you want another message, combine them with IF, like this:
- =IF(COUNTIF(range;"*"&D5&"*")>0;"string FOUND";"string NOT FOUND")
|
|
pus acum 4 ani |
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
To display info from a column / columns from the row the string is found, you can make something similar to:
=IF(COUNTIF(Table2[11];"*"&F1&"*")>0;"this is required to "&INDEX(Table2[3];MATCH("*"&F1&"*";Table2[11];0))&" "&INDEX(Table2[4];MATCH("*"&F1&"*";Table2[11];0))&" from "&INDEX(Table2[5];MATCH("*"&F1&"*";Table2[11];0));"#N/A")
Modificat de TRaP (acum 4 ani)
|
|
pus acum 4 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
You can also do it with a much simpler formula, no ARRAY needed, but no conditions either. Just a simple comparison between two ranges (with NOT, ISNA, VLOOKUP):
And also with ARRAY formula:
The approach might be a bit different.
_______________________________________
|
|
pus acum 4 ani |
|