Hi

I would like to use a function to get the first data point from the 2nd column of a tableWhen values in the 1st column have increased above 1 and stayed above 1 in all subsequent cells.

ie the number 8 would be returned from a vlookup function =vlookup(?>1?,A1:B8,2) in the data set below.

0.9 3

1.0 4

1.1 5

0.9 6

0.9 7

1.0 8

1.1 9

1.2 10

re: I would like to use a function...Go right ahead.

If you need any help, feel free to

ask.

Thats brilliant, I will ask then, thanks I would like to know which function to use in excel to get the first data point from the 2nd column of a table, when the values in the 1st column have increased above 1 and stayed above 1 in all subsequent cells in the list.

i.e. what do I put between the ? ? in this V lookup formula or is there another way?

=vlookup(?>1?,A1:B8,2)

In the data set below, the resultant value would be 8

0.9 , 3

1.0 , 4

1.1 , 5

0.9 , 6

0.9 , 7

1.0 , 8

1.1 , 9

1.2 , 10Thanks in advance for any helpful responses

There is nothing you can put in a VLOOKUP function that will get you the results you want. There is no built-in Excel function that will get you the results you want.

You will need to use a User Defined Function (UDF)

1 - Open the VBA editor.

2 - Click Insert...Module

3 - Paste the following code into the pane that opens.

4 - With your table in starting in A1, enter =FirstOne() in any cell.Function FirstOne() Application.Volatile 'Find last row in Column A lstRow = Range("A" & Rows.Count).End(xlUp).Row 'Loop through range For rw = 1 To lstRow 'Is Value >= 1? If Range("A" & rw) >= 1 Then 'If it is, are all values below it >=1? If Not Application.WorksheetFunction.CountIf _ (Range("A" & rw + 1 & ":A" & lstRow), "<1") > 0 Then 'If yes, then we're done Exit For End If End If Next 'Set Function value to corresponding cell in Column B FirstOne = Range("B" & rw) End Function

Great thanks

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History