Excel - Return value based in two conditions

I need to get the cross values beetween 2 columns:

i only can with auxiliary column, is possible to get in one cell formula?

Thks in advance

Try using <kbd>XLOOKUP( )</kbd> with <kbd>FILTER( )</kbd> function

• Formula used in cell C1

=XLOOKUP(A1,$E$2:$E$7,FILTER($F$2:$K$7,$F$1:$K$1=B1))

Or, if you like to Spill the formula then using <kbd>MAP( )</kbd>

• Formula used in cell C1

=MAP(A1:A6,B1:B6,LAMBDA(x,y,XLOOKUP(x,E2:E7,FILTER(F2:K7,F1:K1=y))))

Or, simple <kbd>VLOOKUP( )</kbd> with <kbd>MATCH( )</kbd> function

• Formula used in cell C1

=VLOOKUP(A1,$E$2:$K$7,MATCH(B1,$E$1:$K$1,0),0)

Lots of great ways to do this, but I recommend using the UNPIVOT lambda here:
https://gist.github.com/ExcelRobot/54fe4bf04f24a83b83d3b3bddda4d3c3

Since SO answers prefer embedded content rather than links, I've included the function definitions below.

Don't be put off by the length of the function. You don't need to worry about how it works, although if you really want to understand it, you're welcome to read this article I wrote explaining it.

Install the Excel Labs Add-in so you can use the Advanced Formula Environment. The add-in is developed by Microsoft Research and is definitely trustworthy. After that's installed, you'll have the benefit of using it on any function or formula going forward. It's worth a few extra minutes.

To get the UNPIVOT function into your workbook, use the "Import from URL" button in Excel Labs:

Paste the link into the dialog and click 'Import':

After the function is imported, hit Save in Excel Labs:

Now you can just enter this formula to unpivot your table:

=UNPIVOT(A1:G7,B1:G1)

/*
Name: Unpivot Table (UNPIVOT)
Description: Given a table range with headers and array of header names, unpivots the 
   specified columns in place, optionally removing any blank entries.
Written By: Excel Robot (@ExcelRobot)
Category: Array
*/
UNPIVOT = LAMBDA(table, columns_to_unpivot, [attribute_name], [value_name], [remove_blanks], LET(
   _AttributeLabel, IF(ISOMITTED(attribute_name), "Attribute", attribute_name),
   _ValueLabel, IF(ISOMITTED(value_name), "Value", value_name),
   _FirstColumnToUnpivot, MATCH(INDEX(columns_to_unpivot, , 1), INDEX(table, 1, ), 0),
   _UnpivotColumnCount, COLUMNS(columns_to_unpivot),
   _ColumnNumbers, SEQUENCE(1, COLUMNS(table)),
   _IncludeColumns, (_ColumnNumbers >= _FirstColumnToUnpivot) * (_ColumnNumbers < _FirstColumnToUnpivot + _UnpivotColumnCount),
   _UnpivotColumns, FILTER(_ColumnNumbers, _IncludeColumns),
   _OtherColumns, FILTER(_ColumnNumbers, NOT(_IncludeColumns)),
   _FullOuterJoin, CROSSJOIN(CHOOSECOLS(table, _OtherColumns), VSTACK(_AttributeLabel, TRANSPOSE(columns_to_unpivot)),TRUE),
   _WithValues, HSTACK(_FullOuterJoin, VSTACK(_ValueLabel, TOCOL(DROP(CHOOSECOLS(table, _UnpivotColumns), 1)))),
   _RemoveBlanks, IF(OR(ISOMITTED(remove_blanks), remove_blanks), FILTER(_WithValues, INDEX(_WithValues, , COLUMNS(_WithValues)) <> ""), IF(_WithValues = "", "", _WithValues)),
   _ColumnOrder, LET(
      n, COLUMNS(_RemoveBlanks),
      s, SEQUENCE(1, n),
      IFS(
         s < _FirstColumnToUnpivot, s,
         s < _FirstColumnToUnpivot + 2, s + n - _FirstColumnToUnpivot - 1,
         TRUE, s - 2
      )
   ),
   _ReorderColumns, CHOOSECOLS(_RemoveBlanks, _ColumnOrder),
   _ReorderColumns
));

/*
Name: Cross Join Tables (CROSSJOIN)
Description: Given two tables, returns the Cartesian product of rows from the tables.
Written By: Excel Robot (@ExcelRobot)
Category: Array
*/
CROSSJOIN = LAMBDA(table1, table2, [has_headers], LET(
   _HasHeaders, IF(ISOMITTED(has_headers), TRUE, has_headers),
   _Data1, IF(_HasHeaders, DROP(table1,1), table1),
   _Data2, IF(_HasHeaders, DROP(table2,1), table2),
   _D1Rows, ROWS(_Data1),
   _D1Cols, COLUMNS(_Data1),
   _D2Rows, ROWS(_Data2),
   _D2Cols, COLUMNS(_Data2),
   _OuterJoinedData, MAKEARRAY(_D1Rows * _D2Rows, _D1Cols + _D2Cols,LAMBDA(i, j,
      IF(j <= _D1Cols, INDEX(_Data1, ROUNDUP(i / _D2Rows, 0), j), INDEX(_Data2, MOD(i - 1, _D2Rows) + 1, j - _D1Cols)))),
   _WithHeader, IF(_HasHeaders, VSTACK(HSTACK(TAKE(table1, 1), TAKE(table2, 1)), _OuterJoinedData), _OuterJoinedData),
   _WithHeader
));

Formula is: =INDEX($E$1:$K$7;MATCH(A1;$E$1:$E$7;0);MATCH(B1;$E$1:$K$1;0))

Notice this will work only if columns used to get the crossed value got no duplicates

You can also use the Offset() function, starting from $D$1, and going the right amount of cells to the right and to the bottom:

=OFFSET($D$1,MATCH(A1,$D$2:$D$7,0),MATCH(B1,$E$1:$J$1,0))

Explanation:

• MATCH(A1,$D$2:$D$7,0): at which place can you find "A1" in the list of letters? (This becomes the number of rows to go down from "$D$1")
• MATCH(B1,$E$1:$J$1,0): at which place can you find "B1" in the list of digits? (This becomes the number of columns to go to the right from "$D$1")

The INDEX-function is just about tailor-made for this.
Your horizontal array seem to be an index already, and if that holds for the actual data you can simply use this one.

However if the horizontal array is not a sequence of 1, 2, 3 and so then use MATCH for both arrays, like this:

I didn't use "$" in the example for better readability, but you want to apply that for the formula to function to work when you copy it downwards.

Double FILTER also could do the job:

=FILTER(FILTER($E$2:$J$7,$D$2:$D$7=A1),$E$1:$J$1=B1)

Result: