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

Dataset Overview

We have a dataset with columns for State Name (Column B), Budget Allocation (Column C), and Region (Column D). We’ll identify the first value in the Budget Allocation column that exceeds a specific threshold.

Method 1 – Using Excel INDEX and 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:

• 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 the MATCH Function to Find the First Value Greater Than Zero

• 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):

• 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.

Read More: Find Last Value in Column Greater than Zero in Excel

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, then select Visual Basic. If you don’t see the Developer tab, follow this link to display the Developer tab on the ribbon.

• • 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 the dataset, the first value greater than 700000 in Column C is 800584.
  • Although the value 794000 in Cell C11 is the smallest 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 the VLOOKUP Function

• 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:
  • We want to find the first value greater than anything related to Texas.
  • In Cell B15, enter Texas as your reference value.

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

• • • 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.

Practice Section

We have included an additional sheet for you to practice with.

Download the Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Find Multiple Values in Excel
• How to Find First Occurrence of a Value in a Range in Excel
• How to Find Value in Column in Excel
• How to Find First Occurrence of a Value in a Column in Excel
• How to Find Last Occurrence of a Value in a Column in Excel

<< Go Back to Find Value in Range | Excel Range | Learn Excel