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.

Find First Value Greater Than Excel


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:
=INDEX(C5:C12,MATCH(TRUE,INDEX(C5:C12>700000,0),))

Find First Value Greater Than Excel

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

excel find first value greater than

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

excel find first value greater than

    • 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):
=MATCH(TRUE,INDEX($C5:$C12>0,0),0)

excel find first value greater than

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

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

excel find first value greater than

  • 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:
=VLOOKUP(B15,B4:D12,2,FALSE)

excel find first value greater than

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

excel find first value greater than

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

excel find first value greater than


Download the Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Asif Khan Pranto
Asif Khan Pranto

Md. Asif Khan Pranto worked as an Excel and VBA Content Developer in Exceldemy for over two years and published some articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical Engineering from the Islamic University of Technology. Now, he is pursuing a Master of Development Studies to experience a new spectrum of knowledge. Apart from creating Excel tutorials, he is interested in Data Analysis... Read Full Bio

2 Comments
  1. In Method one, you were able to find the first budget greater than $700,000. The answer was $800,584.

    How to have the result as Virginia (the State Name)?

    • Hello Ckwong,

      To get the result as State Name. You need to use the state name column as an array of INDEX function. Just replace the cell range from C5:C12 to B5:B12.
      You can use the following formula:
      =INDEX(B5:B12, MATCH(TRUE, INDEX(C5:C12>700000, 0), 0))

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo