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:
=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 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)
 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, locationwise, 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:
=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.
 Here’s what each part of the formula means:

 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
Get FREE Advanced Excel Exercises with Solutions!
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