1

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:

3
  • 1
    You haven't yet provided the whole picture. For one, the image you show doesn't show any sheet-name or range information. Is it in the same sheet as where you'd be entering variable data in A1 and A2? A different sheet? Are you trying to return a result for just one cell (A3 in some sheet); or are you trying to return multiple such results in a contiguous range? I suggest that you share a link to a sample spreadsheet containing realistic data where it will actually reside and your hand-entered result(s) where you'd like to see it/them returned via formula.
    – Erik Tyler
    Commented Sep 13, 2022 at 6:44
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Sep 13, 2022 at 21:35
  • Sorry for the blurry explanation. I have provided a google sheets 'test' environment with the situation. docs.google.com/spreadsheets/d/… I hope it's a little more clear. So I need to find the corespoding table value from 'DATA sheet' and enter it in column E from 'Work Sheet' looking at CM type and the value entered on 'D' Tx!
    – Rip
    Commented Sep 14, 2022 at 6:50

1 Answer 1

0

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!

2
  • Thank you very much for the reponse. Still, I'm having a bit of problems with the formulas. I've followed the indications: I've created an extra column with the values as indicated by you "0, 501, etc". I've created a test spreadsheet containing 2 sheets, the data one (with the situation and the values) and another one with the A1 and A2 information. I've used the first formula with offset but to no avail. docs.google.com/spreadsheets/d/… Entered the formula but it's not returning anything.
    – Rip
    Commented Sep 14, 2022 at 12:02
  • Please try in F5 =IFERROR(OFFSET('Data sheet'!$C$4,MATCH($D5,'Data sheet'!$C$5:$C,1),MATCH($C5,'Data sheet'!$D$4:$4,0)),"Not found")
    – JohnSUN
    Commented Sep 14, 2022 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.