Get value of steps

2

I wanted to know which formula (s) to get a value of a rank from a predefined table.

For example, consider that in a worksheet I have the following table:

| A   |  B   |
|-----|------|
| 245 | 4,5% |
| 256 | 6%   |
| 300 | 7%   |

So, when typing in any other cell, for example, 256 , I would like a formula that would automatically find the corresponding rank value in the table (in this case, 6% ).

Explaining better: putting a value between 0 and 245 was returned 4.5 if you put a value between 246 and 256 it was returned 6% and so on.

    
asked by anonymous 23.12.2015 / 12:11

1 answer

3

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):

    
24.12.2015 / 23:13