In this article, we will learn about Excel Find First Value Greater Than any specified Value using a few easy formulas.
Suppose we have a dataset of fund allocation for a project having State Name in Column B, Budget Allocation in Column C and region in Column D. Here want to find out the First Value Greater Than a specific Value. We will use the Array formula & VBA to find this.
How to Find First Value Greater Than in Excel: 4 Easy Ways
1. Find First Value Greater Than Specified Value Using Excel INDEX & MATCH Functions
In this method, I will show you how to Find First Value Greater than a specified Value in Excel. I will use MATCH & INDEX formulas together to find it.
Steps:
- Suppose from the above dataset we want to find out the First Value in Budget Allocation which is Greater Than $700,000.00.
- To do this we have to type the formula in B15.
=INDEX(C5:C12,MATCH(TRUE,INDEX(C5:C12>700000,0),))
- Here, the digit after the Greater Than symbol (>) is the specified Value & we want the First Value Greater Than that specified Value.
- Here in the formula, I am looking for the First Value Greater than 700000.
- Also, I have selected the Range where I want Excel to look for data.
- I have selected Cell C5:C12 from the above dataset.
The last Zero in the formula stands for the MATCH Formula Argument, where 0 (Zero) means to look for an exact match.
- Upon pressing ENTER after typing the above formula we will find our result & which is 800584 from Cell C8.
- Though there are more values in the table which is Greater Than 700000 & lesser than 800584. 800584 is the First Value Greater Than our specified Value.
2. Using Excel MATCH Function to Find First Value Greater Than Zero
In this method, we will learn how to Find First Cell Greater Than Zero using the MATCH function along with the INDEX function.
Steps:
- In the dataset shown above, I want to find the First Value with Cell of Occurance Greater Than Zero.
- Here my data Range is C5:C12.
- Now type the formula shown below in any Cell where you want to see the result. I have selected Cell B15.
=MATCH(TRUE,INDEX($C5:$C12>0,0),0)
- Now the formula will return 3 in Cell B15.
- Now we will have to look for Cell No 3 in our selected Range of C5:C12.
- So, Cell 3 of our data Range has the First Value Greater Than Zero.
Read More: Find Last Value in Column Greater than Zero in Excel
3 . Employing VBA to Find First Value Greater Than Specified Value in a Range
In this method, I will demonstrate to you how to Find First Value Greater Than a specified value in a Range using VBA.
Steps:
- To use VBA first click the Developer tab & then click on Visual Basic. If you don’t see the Developer tab, then, follow this link to display the Developer tab on the ribbon.
- Then follow Insert >>Â Module.
- Now one new Module will be opened. it is Module 2.
- To rename your Module follow View >> Properties Window.
- Then rename your Module in the Properties box.
- I have renamed mine Greater_Than.
- Now type the following code in your Module.
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
- Here, I created a Sub Procedure Sub Great(), where declared two variables I as Integer and Cells_Value as String using Dim Statement. You can declare any as you wish.
- Then, I used the For loop to check our Row Range which is 5 to 12.
- Since I want to see the First value Greater Than 700000, I used the IF statement to check the condition. Type your own specified Value.
- Since I want Excel to look for data from Column C or Column no 3, I have typed 3 wherein Cells.
- I used a MsgBox to show the first value greater than.
- While using it, edit it according to your dataset location & specified value & Run.
- Click on the Run.
- You can also use Keyboard Shortcut F5.
- Now a box will appear & show you the First Value Greater than 700000 or a specified value in the VBA Code.
- In my dataset, the First Value Greater than 700000 in Column C is 800584.
- Though 794000 of Cell C11 is the First Numeric Value Greater Than 700000 in Column C, location wise 800584 is the First Value from the beginning of Column.
4. Find First Value Greater Than Anything Using VLOOKUP Function
In this section of the article, we will see how to Find the First Value Greater Than anything but it requires another operation of data sorting before applying the VLOOKUP function.
Steps:
- To Find First Value Greater Than anything using VLOOKUP first Sort your data from High to Low.
- To find the First Value Greater Than anything of Texas I have typed Texas in Cell B15.
- Then select any Cell where you want to see the result & there type the following formula.
=VLOOKUP(B15,B4:D12,2,FALSE)
- Here, B15 is the reference Value.
- In my dataset, I am looking for the First Value of Texas. I have written Texas in Cell B15. That’s why I have typed B15 at the beginning of the formula.
- B4:D12 is my dataset Range. Select your own data Range.
- Then col_index_num 2 stands for the 2nd Column in my dataset. This formula will look for data in the 2nd column of my dataset with reference to Cell B15 or Texas.
- The last word stands for Argument. False argument calls for finding the exact same.
- Now applying the formula above, I have got the result 500000 which is the First Value for Texas also Greater Than anything as we have Sorted our data initially.
Practice Section
I have provided an extra Sheet for you to practice. Enjoy it.
Download Practice Workbook
Conclusion
Reading the article above we have learned about Excel Find First Value Greater Than Specified Value, Zero, or Anything. I hope this article will help you to find the desired result from your dataset & make your work easier. If you have any doubt just contact us.
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!