Excel - how to return next highest figure in a column but only from a row below that specific number
I’ve found a number of threads to return the next largest number from a column and have obtained that using a combination of index, match, small and rank functions. However, I only want to return values from rows below a specified row.
Below is an example table. I’d like to return 12 from here when trying to obtain the next largest value after L2’s 278.
| contract | qty |
|---|---|
| L 1 | 25 |
| L 2 | 278 |
| L 3 | 12 |
| L 4 | 6 |
It is not possible to remove unwanted rows from the table as this is a constantly refreshing table of available data.
I used:
=INDEX(Table1[contract],MATCH(IFERROR(SMALL(Table1[qty],RANK.EQ(VLOOKUP(“L2”,Table1[[contract]:[qty]],2, FALSE),Table1[qty],1)+1,SMALL(Table1[qty],RANK.EQ(VLOOKUP(“L2”,Table1[[contract]:[qty]],2, FALSE),Table1[qty],1)-1)),Table1[qty],0)
Solution
This formula:
=MAX(INDEX(A2:B8,MATCH(D1,A2:A8,0)+1,2):B8)
the cell address B8 is to be replaced the last cell address in column B.
A2:B8 and A2:A8 are the respective ranges of data and column A.
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table