Excel Tips

Visualize complex expressions (e.g. nested IFs)

https://www.excelformulabeautifier.com/

Distinct and Unique values

These examples assume a range of values at A1 to A999

_________________________________________________________________________________________________________
What you want to find  |Speed*| Function to use
-----------------------+------+--------------------------------------------------------------------------
Distinct numeric values FAST  =SUM(IF(FREQUENCY(A1:A999;A1:A999)>0;1))
Distinct values         SLOW  =SUM(IF(FREQUENCY(MATCH(A1:A999;A1:A999;0);MATCH(A1:A999;A1:A999;0))>0;1))
Unique numeric values   SLOW  =SUM(IF(ISNUMBER(A1:A999)*COUNTIF(A1:A999;A1:A999)=1;1;0))
Unique values          *SNAIL* =SUM(IF(1/COUNTIF(A1:A999;A1:A999)=1;1;0))

*: speed will not be an issue for hundrends of values but for thousands it will

Simple lookup

If you have two columns, one with names and one with emails and you want to get the email that coresponds to a given name then:

|      A   |   B                |   C
|----------+--------------------+----------------------------------------------
|   Name   | email              |
|----------+--------------------+----------------------------------------------
| Nick     | ndemou@enlogic.gr  | =INDEX ( B:B ; MATCH ( "Nick" ; A:A ; 0 ))
| Aggeliki | anag@enlogic.gr    |
| Christos | clivas@enlogic.gr  |

Here's another way to look at it:

 =INDEX ( B:B ; MATCH ( "Nick" ; A:A ; 0 ))
         +---           -----+  --+--
          \   what you know_/      \___ column with values like what you know
           \ 
            \__ column with values you are trying to return