# 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:
`=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.

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

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

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

## Related Articles

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

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

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

Advanced Excel Exercises with Solutions PDF