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?
Oct 9, 2022 in Others 531 views

## 1 answer to this question.

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.

• 63,700 points

## Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

## How to lock data in excel sheet using POI, leaving cells without any data / the rest of the sheet unlocked

If the cells in a row have ...READ MORE

## How search data in excel with openpyxl?

The method iter_rows in the library has changed, refer ...READ MORE

## Create charts in excel sheet [ in same sheet along with data C#]

Unfortunately, it's not that simple. Use the ...READ MORE

## Excel Lookup function for text

Use a simple VLOOKUP() function like: =VLOOKUP(E2,\$A\$2:\$B\$6,2,FALSE) You can also use ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE