lookup Data in Excel

0 votes
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 in Others by Kithuzzz
• 20,660 points
56 views

1 answer to this question.

0 votes

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.

answered Oct 9 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

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

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

answered Sep 23 in Others by narikkadan
• 37,660 points
107 views
0 votes
1 answer

How search data in excel with openpyxl?

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

answered Oct 2 in Others by narikkadan
• 37,660 points
266 views
0 votes
1 answer

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

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

answered Oct 11 in Others by narikkadan
• 37,660 points
67 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
454 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

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

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
405 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,384 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
77 views
0 votes
1 answer

Removing specific rows in an Excel file using Azure Data Factory

Under the 'Source' tab, choose the number ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
243 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP