Making a lookup table

[richard7893]

Hello,

I have an equation that depends on two variables. I want to make something analogous to a multiplication table, where you find the intersecting row & column and get the result. In my case, I want to use the intersecting row and column where my equation takes the value from that row and column where the intersection occurs, and gives the result. How can this be done? Any help would be appreciated

[Lupp]

This is simple in many specific cases, but generally such operation tables are subject to reservations.
At least operation tables have to be 2D, and are thus restricted to binary operations.
Assuming your operands are to be taken from ranges with a maximum of 1023 different values the first one and with a probably greater, but still reasonable number of values the second one, you can:

0. Dedicate a sheet, say ‘OT’ for the OperationTable.
1. Enter the values for the first operand into B1, C1 … (up to AMJ1) in ascending order.
2. Enter the values for the second operand into A2, A3 … (up to A1048576) in ascending order.
3. Enter the results into the cells at the interjections regarding the values in the first row and in the first column.
4. Start using the table thus after having entered a maximum of 1073741824 items.

Assuming you want to get the result for x OP y, where x is a value occurring in the first row, y a value occurring in the first column, and OP the operation you tabulated in the sheet OT, you can use now
=OFFSET($OT.$A$1;MATCH(x;$OT.$A$1:$AMJ$1;0)-1;MATCH(x;$OT.$A$1:$A$1048576;0)-1)
Using INDEX() instead of OFFSET() you have to remove the -1 for both the coordinates.
There will surely be some simplifications applicable in most specific cases.