I have this predefined table
| Exposed People| Exposed People| Exposure to risk | ... | NR Index | | -- | -- | -------- | -- | -- | | Li | Ls | | | | | 1 | 4 | very low | .. | 1 | | 5 | 10 | low | .. | 3 | | 11 | 15 | high | .. | 5 | | 16 | 20 | very high | .. | 9 | ... The table can increase in size in both dimensions or after some time i can redefine the indexes NR. The table was called "Mproba" (Interval with name) The parameters are "Exposed P.", "Exposure".... "NR" is the last column that has the indexes (1,2,3,4..) in according of the value that user put in a cell (parameter).
In a separated sheet, the user must put a value in according with the parameter. Example: cell E8: Exposed P. = 17 Cell E9: Exposure = low
to get the index (NR) of each value I used this formula:
For "Exposed P." =ArrayFormula(SUM(IF((CHOOSECOLS(Mproba,1)<=E8)*(E8<=CHOOSECOLS(Mproba,2)),INDEX(Mproba,ROW(Mproba),COLUMNS(Mproba)),0))) however i got error.
for "Exposure to Risk" =INDICE(CHOOSECOLS(Mproba,COLUMNAS(Mproba)),COINCIDIR(E9, CHOOSECOLS(Mproba,3),0),1) It works.
please, i would like That you can give me the correct formula of "Exposed P" and to find its index NR.