VLOOKUP to calculate a limited range in a datatable

English
#1

I have large spreadsheet that tracks a rover on Mars. It has an existing report that extracts relevant data each time the rover completes a drive. But I am developing a new report to provide a snapshot from any earlier drive. I can get most of the data I need for my report by using VLOOKUP that looks at a value from a drop-down list. However I am struggling to find a formula that will provide the MAX or MIN data in the reduced ranges of specific columns.
Example: I want to establish the minimum number from range M3:M# where M# is on the same row of my sheet as my search criteria in my drop down list.
Can this be done in a VLOOKUP formula, or might I need helper cells?
M20 July 2026 1922.ods (1.2 MB)
EDIT: I have uploaded the file.

There are several tabs.

The report is on the Report tab, the first data I need is on the Wjson tab in column M, once I can make that one work, I can revise the formula to make the others

On the report tab you can see a drop down list in cell H1, this is used in several other formulas in column H (H3:H55) to get data for the new report from 2 tabs. Many of those formulas use VLOOKUPs to get specific values. My aim is to add a similar function to just use a limited range, ie from the first row of data in column N to the associated row to whatever is in the drop down box
The formula in H4 [=MAX(Wjson.M:M)] gives me Max value for the whole mission. The formula I need will produce the MAX value up to the drop-down selection. I hope that makes sense

#2

I’d say you need HLOOKUP to search a ROW.

1 Like
#3

@PaulHam
XLOOKUP

2 Likes
#4

Missing info

It would be useful to have available a spreadsheet file which shows your data structure. This relieves us from some guesswork, and thus makes it easier for us to help you. Please upload a sample! (Data itself may be mangled/randomized in the sample if you are not at liberty to reveal your actual content.)

At best, upload an attachment to your original posting. To edit, click the pen icon at the lower right corner of your question post. The upload tool is in the middle of the edit pane toolbar: bilde

Some general pointers

  • To determine the lowest value from a range, use the MIN() function:
    =MIN(M3:M#)
  • To specify a range dynamically (assuming that your desired # may change over time), use the OFFSET() function:
    =MIN(OFFSET(M3;0;0;#-2;1))
  • To determine which row the lowest value is on (for further processing or to extract background info), use the MATCH() function:
    =MATCH(MIN(OFFSET(M3;0;0;#-2;1));OFFSET(M3;0;0;#-2;1);0)

Please note:

  • Without the detail available as mentioned above, I am only guessing that the suggested functions are useful for your purpose and that example formulas are relevant to your spreadsheet.
  • As indicated in examples, functions can be combined quite freely in formulas. In most cases, intermediate calculations in helper cells should still be favored over complex “one-liners”.
  • The # character in the sample formulas must be replaced by your chosen row number (be it a constant number, a cell containing a number, or a calculation).
2 Likes
#5

I have added the spreadsheet and tried to explain what I am looking for :slight_smile:

#6

I attached your file with my suggestions inserted. See below.

Guesses/assumptions

  • Since the dropdown source is built from Tjson column H, which has the same heading as Wjson column E, I guess that the dropdown selection pertains to Wjson column E.

  • Your dataset is static (at least in size).

  • Wjson column E appears to be sorted, but there are some duplicates (no duplicates in the Tjson sheet).

Strategy

To determine the row number where the search should stop, use the MATCH() function. I have assumed that the list is static and ends at data row 686 (sheet row 688).
=MATCH(H1;$Wjson.E3:E688;1) which I entered in Report.I1

In the case of duplicates

  • My limited testing indicates that matching based on ascending sort always returns the last element of a repeated value in the dataset, but I cannot find this behaviour explicitly documented. To ensure that search covers all elements with the selected identifier number, a “fudge factor” can be added (indicating that the target value is “between the lines”, as it were).
    Assuming all integers …
    =MATCH(H1+1/2;$Wjson.E3:E688;1)
  • If limit should be set at first occurrence of the selected number, use “unsorted”/exact matching instead. According to available documentation, this will consistently return the first match.
    =MATCH(H1;$Wjson.E3:E688;0)

If your dataset is dynamic (may grow or shrink), a few extra steps are required in order to reliably determine the location at which to stop searching.

The highest value is then found by the MAX() function, applied to a range returned from the OFFSET() function:
=MAX(OFFSET($Wjson.M3;0;0;I1;1)) as entered in cell Report.J1 in attachment.

I also added a formula in report.K1 to return the location of the max value in the dataset.

M20 augmented.ods (1.3 MB)

1 Like
#7

Many, many thanks Keme1,

After some testing your solution appears to be exactly what I am looking for.

The data set is dynamic it will grow in size each time the rover drives on Mars. A single row is added to Tjson at each revision, but Wjson can sometimes have two partially duplicate entries with some minor changes, but for the purpose of my requirement, I don’t believe it is an issue, but I’ll need additional testing to ensure that assumption holds true.

I can readily adjust the formulas you have provided to extract the other fields of data I need for MIN values as well as some dynamic averages as well.

Thank you once again :slight_smile:

#8

Good to hear!

In that case, you may be best served with the “exact matching” to provide the dataset size for OFFSET() to apply (using the last parameter of zero for MATCH(), which stops at the first match so it will discard subsequent duplicate rows). You can then supply the full “possible range” size at the outset, which will make a simpler set of formulas.

If you find that you need the report to cater for all data rows with duplicate “identifiers”, I guess you will need to use OFFSET() on two levels: first to provide the search range for MATCH(), then to provide the assessment range for MAX() (or other evaluating functions you want to apply).

There are other strategies towards achieving reliable automated reports, but I find that the more common ways chosen to such end (multiple level IFs and the use of INDIRECT() ) quickly escalates to unmanageable proportions, and also loses portability (if you want to share your work e.g. with users of MS Excel or Google Sheets).

1 Like
#9

Thanks again, I will change the last parameter to zero to force the exact match.

You would not believe the amount of time I spent searching for a solution to this issue. With you valuable help I can now finish the task I set out to do, and knowing how to set a range using MATCH, I can now foresee some additional analysis for specific reports that are now possible.

I really appreciate your help in this matter

Best regards

Paul

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.