How to Find First Value Greater Than in Excel (4 Ways)

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.

Find First Value Greater Than Excel


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),))

Find First Value Greater Than Excel

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

excel find first value greater than

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

excel find first value greater than

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

excel find first value greater than

  • 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


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 First Value Greater Than anything using VLOOKUP first Sort your data from High to Low.

excel find first value greater than

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

excel find first value greater than

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

excel find first value greater than

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

excel find first value greater than


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

Asif Khan Pranto

Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo