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, 2022 in Others by Kithuzzz
• 38,020 points
686 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, 2022 by narikkadan
• 63,720 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, 2022 in Others by narikkadan
• 63,720 points
850 views
0 votes
1 answer
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, 2022 in Others by narikkadan
• 63,720 points
6,909 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, 2022 in Others by narikkadan
• 63,720 points
1,105 views
0 votes
1 answer
0 votes
1 answer

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

answered Dec 13, 2022 in Others by narikkadan
• 63,720 points
523 views
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,740 points
1,105 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
1,026 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, 2022 in Others by narikkadan
• 63,720 points
708 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, 2022 in Others by narikkadan
• 63,720 points
2,487 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