lookup Data in Excel

Question

In Excel, I have a 100x100 data table with two variables.

I require a function that outputs every conceivable combination of variables that could produce a particular goal value. I believe I am looking at a recursive two-dimensional lookup function. Can you direct me to the proper route, please?

Answer 1

Without VBA, it can be done in a fairly compact manner like this. Imagine that your 100x100 table is at B2:CW101 and that we have a list of numbers from 1 to 100 running down the left side from A2 to A101 and again from B1 to CW1 across the top.

Make a column of cells beneath it, beginning, let's say, with B104.

 B104=MAX(($A$2:$A$101*100+$B$1:$CW$1<B103)*($B$2:$CW$101=TargetValue)*($A$2:$A$101*100+$B$1:$CW$1))

This is an "array" formula, so press Ctrl-Shift-Enter instead of Enter, and curly brackets {} should appear around the formula.

Then copy down for however many rows you may require. Additionally, you must add a big number, such as 999999, above your initial formula, which is in B103.

The formula calculates Rowx100+Column, but only for each successful cell, and the MAX function finds the largest result, excluding any previously discovered results, i.e., it discovers the target results one at a time, moving from bottom right to top left. (With a little work, you could get it to look in the opposite direction.)

Results like 9922, which corresponds to row 99 and column 22, will be returned to you. You can easily deduce these values from the number.