How to Find the First Value Greater Than in Excel (4 Methods)

In this article, we’ll explore four methods to find the first value greater than a specified amount in an Excel dataset. Suppose we have a dataset with columns for State Name (Column B), Budget Allocation (Column C), and Region (Column D). Our goal is to identify the first value in the Budget Allocation column that exceeds a specific threshold. We’ll use a combination of Excel functions to achieve this.

Method 1: Using Excel INDEX & MATCH Functions

Scenario:
- We want to find the first value in the Budget Allocation column that is greater than $700,000.00.

Formula:
- Enter the following formula in cell B15:

=INDEX(C5:C12,MATCH(TRUE,INDEX(C5:C12>700000,0),))

Explanation:
- The INDEX function returns the value from the Budget Allocation column (C5:C12) based on the position specified by the MATCH function.
- The inner INDEX(C5:C12>700000,0) creates an array of TRUE and FALSE values, where TRUE corresponds to values greater than $700,000.00.
- The outer MATCH(TRUE, . . .) finds the position of the first TRUE value in the array (i.e., the first value greater than $700,000.00).

- The RANGE, where Excel should search for data, is Cell C5:C12.

- The final zero in the formula represents the argument for the MATCH function, where 0 (zero) indicates an exact match.

- The result is the value 800584 from cell C8, which is the first value greater than the specified threshold.

Note:
- There may be other values in the table greater than $700,000.00 but less than 800584. However, 800584 is the first value that meets our criteria.

Method 2 – Using Excel MATCH Function to Find the First Value Greater Than Zero

Steps:

Scenario:
- We want to identify the first value greater than zero in the dataset provided above.

Data Range:
- Our data range is C5:C12.

Formula:
- Enter the following formula in any cell where you want to see the result (e.g., Cell B15):

=MATCH(TRUE,INDEX($C5:$C12>0,0),0)

Explanation:
- The INDEX function creates an array of TRUE and FALSE values based on whether each value in the data range is greater than zero.
- The outer MATCH(TRUE, . . . ) finds the position of the first TRUE value in the array (i.e., the first value greater than zero).
- The result is 3 in Cell B15, indicating that the third cell in our selected range (C5:C12) contains the first value greater than zero.

Method 3 – Using VBA to Find the First Value Greater Than a Specified Value in a Range

Steps:

Accessing VBA:
- Click the Developer tab in Excel, and then select Visual Basic. If you don’t see the Developer tab, follow this link to display the Developer tab on the ribbon.

- Next, go to Insert > Module to open a new module (Module 2).

Renaming the Module:
- To rename the module, go to View > Properties Window.

- In the Properties box, rename the module (e.g., Greater_Than).

Writing the VBA Code:
- Within the module, create a Sub Procedure named Sub Great().
- Declare two variables: I as an integer and Cells_Value as a string using the Dim statement. You can choose other variable names if desired.
- Use a For loop to iterate through the row range (from 5 to 12).
- Since we want to find the first value greater than 700000, use an IF statement to check this condition. Replace 700000 with your specified value.
- Specify that Excel should look for data in Column C (Column number 3) by using the Cells function.
- Display the first value greater than the specified threshold using a MsgBox.

Sub Great()
Dim i As Integer
Dim Cells_Value As String
For i = 5 To 12
If Cells(i, 3).Value > 4660 Then
Cells_Value = Cells(i, 3).Value
MsgBox "Value is " & Cells_Value
Exit For
End If
Next
End Sub

Running the Code:
- Edit the code according to your dataset location and specified value.

- Click the Run button or use the keyboard shortcut F5.

- A message box will appear, showing the first value greater than 700000 (or your specified value) based on the VBA code.

Result:
- In my dataset, the first value greater than 700000 in Column C is 800584.
- Note that although the value 794000 in Cell C11 is the first numeric value greater than 700000 in Column C, location-wise, 800584 is the first value from the beginning of the column.

Method 4 – Find the First Value Greater Than Anything Using VLOOKUP Function

Steps:

Sort Your Data from High to Low:
- Arrange your dataset in descending order based on the relevant column (e.g., invoice value).
- This sorting step is essential for accurate results when using VLOOKUP.

Specify the Reference Value:
- Let’s say we want to find the first value greater than anything related to Texas.
- In Cell B15, type Texas as your reference value.

Apply the VLOOKUP Formula:
- Choose any cell where you want to see the result.
- Enter the following formula:

=VLOOKUP(B15,B4:D12,2,FALSE)

- - Here’s what each part of the formula means:
    - B15 : The reference value (in this case, Texas).
    - B4:D12 : Your dataset range. Make sure to select the appropriate range for your data.
    - 2 : Refers to the second column in your dataset. The formula will search for data in this column based on the reference value (Cell B15).
    - FALSE : Indicates an exact match. We want the first value greater than the reference.

Result:
- After applying the formula, you’ll get the result. For example, if the sorted data contains values like 500000, the formula will return that as the first value greater than anything related to Texas.

Remember to adjust the cell references and dataset range according to your specific data. Sorting the data beforehand ensures accurate results when using VLOOKUP.