How to return table value?

Question

I need to do the following:

If cell is "Situation X" then show in cell next to it value from interval and specific situation.

For example:

If cell A1 is "Situation 6" and value in A2 is "800", return in A3 value "69".

I know how to use vlookup in a range, but I don't know how to "double" it so I can look at the range relevant to a certain situation.

Here is the table:

Answer 1

Welcome!

In general, a lookup in your crosstab can be written like this:

=OFFSET(<start_of_table>,MATCH(<value_to_search>,<first_column_of_table>,1),MATCH(<situation_to_search>,<first_row_of_table>,0))

"find the desired row, find the desired column, take the value from the cell offset from the top left cell by the found values"

This is not the only way, just one of many possible ways.

In your case, the task is complicated by the fact that the first column of the table contains text designations of ranges. To search for the desired row, it is enough to have only the beginnings of the ranges. In other words, it would be more rational to represent the {0-500,501-750,751-1000,1001-1250,1251-1500,1501-1750,1751-2000,>2000} column as {0,501,751,1001,1251,1501,1751,2001}. To perform this transformation, you can build a formula similar to

IFERROR(VALUE(REGEXEXTRACT(<first_column_of_table>,"(.*)-")),2001))

Again, this is not the only possible way

And the formula for selecting the desired value in accordance with the values of cells A1 and A2 may look like this:

=ARRAYFORMULA(OFFSET(<$top_left_cell>,MATCH(A2,IFERROR(VALUE(REGEXEXTRACT(<$first_column_of_table>,"(.*)-")),2001),1),MATCH(A1,<$first_row_of_table>,0))) 

Be careful with the third parameter of the MATCH() function - when searching for a row it is equal to 1, when searching for a column it is equal to 0!