As fellow Alexandre had already advanced in one comment , there is a function available in Excel called PROCV
" (in the English version, or VLOOKUP
in the English version) that allows you to do automated searches in tables. The syntax for using this function is as follows:
=PROCV(Valor a ser Buscado;Range da tabela para busca;Índice numérico da coluna a ser devolvida)
Please note that if your version of Excel is in English you must separate
the parameters with a comma ( ,
) instead of a semicolon ( ;
).
To make your solution more automated, I suggest naming your table (for example, using the name Escalões
), and then use that name instead of the range . To do this, just select the table and give a name in the field available to the left side of the formulas, next to ribbon :
Youcan,forexample,usethefollowingcalltogetthedesiredvalue:
=PROCV(D4;Escalões;2)
Notethatthefunctionforthelastrowwithavaluelessthanorequaltotheonesearchedfor,thenworksthewayyouwant(usingthesamerankforintervals).Onlyyouneedtohaveatleastonelinewiththedefaultvalueforvalueslessthan245
(iftheyexist),otherwisetheformulareturnserror(asillustratedbelow):