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:
blah din Galati
 | Femeie
23 ani
Galati
cauta Barbat
27 - 80 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Say you have a wide range of 4000 positions and you would like to look for a 10 values range in the 4000 range and for every match of the 10 values in the 4000 range to display a different text (eg. the name of the 10 range, a number, a sum or whatever).
Here I compared 8 ranges, shortest with 1 value and longest with 31 values to a range of 16000 rows:
=IF(COUNTIF($AI$4:$AI$6;G4);$AI$3;IF(COUNTIF($AJ$4;G4);$AJ$3;IF(COUNTIF($AK$4:$AK$6;G4);$AK$3;IF(COUNTIF($AM$4:$AM$6;G4);$AM$3;IF(COUNTIF($AN$4:$AN$9;G4);$AN$3;IF(COUNTIF($AO$4:$AO$34;G4);$AO$3;IF(COUNTIF($AP$4:$AP$5;G4);$AP$3;IF(COUNTIF($AQ$4:$AQ$8;G4);$AQ$3;""))))))))
So for some of the rows in the 16000 range, it will return the value of AI3, which is the name of a range of 3 values: AI4, AI5 and AI6. For those matching AJ4, it will return the value of AJ3 and so on.
The syntax is =IF(COUNTIF(RANGE;VALUE);DISPLAY;"")
- RANGE = the range of values that must be searched in a big range
- VALUE = the cell of the first value in the big range
- DISPLAY = what needs to be returned if VALUE is found in RANGE
Yes, it's a bit long, but it does the job right.
To make it much simpler, I could add all values in 1 column and name each range of values with its corresponding name (in my case in row 3 cols AI-AQ).
An easy example of this would be:
=IF(COUNTIF(A:A;U2);"";U2)
where:
- A:A is the range containing some values
- U2 is the material to look for.
If U2 is not in range A:A, it will be displayed.
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|