Last active
March 3, 2024 19:02
-
-
Save ExcelRobot/bb205c7214fbbcf5b3a7243ea07e6ebc to your computer and use it in GitHub Desktop.
Two-Way Table Lookup LAMBDA Function
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| Name: Two Way Table Lookup (TLOOKUP) | |
| Description: Looks up values in a table by values in first column and first row. Accepts arrays of column/row values or if omitted will return all columns/rows. | |
| Parameters: | |
| table - range or array that includes headers in first row and column | |
| [row_values] - value or array of values to lookup in first column; returns all rows if omitted | |
| [column_values] - value or array of values to lookup in first row; returns all columns if omitted | |
| Source: Excel Robot (@ExcelRobot) | |
| */ | |
| TLOOKUP =LAMBDA(table, [row_values], [column_values], LET( | |
| _RowIndexes, XMATCH(TOCOL(row_values), DROP(TAKE(table, , 1), 1)), | |
| _ColumnIndexes, XMATCH(TOROW(column_values), DROP(TAKE(table, 1), , 1)), | |
| _Result, INDEX( | |
| DROP(table, 1, 1), | |
| IF( | |
| ISOMITTED(row_values), | |
| SEQUENCE(ROWS(table) - 1), | |
| IF(ROWS(_RowIndexes) = 1, @_RowIndexes, _RowIndexes) | |
| ), | |
| IF( | |
| ISOMITTED(column_values), | |
| SEQUENCE(1, COLUMNS(table) - 1), | |
| IF(COLUMNS(_ColumnIndexes) = 1, @_ColumnIndexes, _ColumnIndexes) | |
| ) | |
| ), | |
| _Result | |
| )); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Examples of use:
