Asif Khan Pranto
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 & 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.
Download Practice Workbook
4 Ways to Find First Value Greater Than in Excel
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.
Read More: How to Find If Cell Contains Specific Text in Excel
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 (2 Easy Formulas)
Similar Readings
- How to Find Multiple Values in Excel (8 Quick Methods)
- How to Find from Right in Excel (6 Methods)
- Excel Function: FIND vs SEARCH (A Comparative Analysis)
- FIND Function Not Working in Excel (4 Reasons with Solutions)
- [Solved!] CTRL+F Not Working in Excel (5 Fixes)
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 it is not available then turn it on using Options.
- 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.
Read More: Find First Occurrence of a Value in a Range in Excel (3 Ways)
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 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.
Read More: How to Find Value In Range in Excel (3 Methods)
Practice Section
I have provided an extra Sheet for you to practice. Enjoy it.
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 If A Range of Cells Contains Specific Text in Excel (4 Methods)
- How to Find Lowest 3 Values in Excel (5 Easy Methods)
- Find External Links in Excel (6 Quick Methods)
- How to Find a Character in String in Excel
- How to Find Last Cell with Value in Column in Excel
- Excel Find Last Column With Data (4 Quick Ways)
