I want to make some kind of Lookup with this kind of table.

Is it possible or any other way to do this?

CodePudding user response：

You do not show your Row numbers or Column letters in your post. But supposing that your search number is in cell F2 and that everything else is arranged relative to that, you can use this rather simple formula in G2:

=ArrayFormula(IFERROR(VLOOKUP(F2,{FLATTEN(B2:C3),FLATTEN(B6:C7)},2,FALSE)))

FLATTEN turns a 2D array into a one-column array, working left-to-right and top-to-bottom, which is perfect for a situation like yours.

It is unclear whether you will only be wanting to search that one number and return one result, or whether you will have several numbers in the search column and wish to get their several results. If the latter, the above formula can easily be modified to handle multiple numbers:

=ArrayFormula(IF(F2:F="",,IFERROR(VLOOKUP(F2:F,{FLATTEN(B2:C3),FLATTEN(B6:C7)},2,FALSE))))

See my comment on your original post as well.

References

• 

  CodePudding user response：

  A simple approach is to use the lookup search_result_array parameter, the trick is to work backwards from highest value columns to smallest due to the search_key not found default behavior of reverting to lower values.

  =ifna(lookup(E2,$C$2:$C$3,$C$6:$C$7),lookup(E2,$B$2:$B$3,$B$6:$B$7))

  

  https://blog.sheetgo.com/google-sheets-formulas/lookup-formula-google-sheets/