Yet another time you will see me say that in statistics 80% of the time is used for obtaining and cleaning data and 20% for the fun parts like data dashboards and models. If we want to make descriptive statistics thanks to an aggregation of the vertical lookup, =VLOOKUP both on Excel and on Google spreadsheets, we need to pay attention to how the value to be searched manifests itself, otherwise we will waste considerable time, or you’ll get bitten nails and trichotillomania.
Value with text format
If you look for a number but the program sees it as text, you can bang your head on the formula as much as you want, which in itself has several points of failure, the problem lies above. And this error of concentration can happen in many areas of life.
If typing =TYPE(cell) results in a value other than 1, which indicates a number, we need to forcibly convert that cell with =VALUE(cell). But since when it rains poop it doesn’t make two drops, the previous step may not be enough, because the program has valid reasons for seeing that cell as text. Maybe the cell contains spaces, more or less special characters that we can reveal only if we see the formula by clicking on the value cell. At that point the heavy artillery of computer scientists is needed, for example the formula =REGEXEXTRACT(cell), possibly combined with VALUE outside. Or in any case a declination of the formula that uses REGEX.
If you want to avoid this problem and others, through a free call we can do a statistical consultation for some of your cases.