Kypros 13:37 18 Feb 2003

I need a formula which will detect the first instance of a number which is less than 0 in a column of numbers and return that number and a number from the cell 4 cells to the left of it, and place those numbers in two cells.
Example: find first instance of negative number in array F5:F50 and place in D1. Find corresponding value from cell in column B and place in D2.
Can anyone help out here?

  recap 19:21 18 Feb 2003


  Paroxetine 19:21 18 Feb 2003

have you tried asking Mr excel.com? they can be quite helpful.

Also there was a posting in here a few months back about a spreadsheet that would show u all formulas and how to use them. Maybe that could be used as a foundation to build on something.

Sorry I cant be much help, my maths are bad as it is without getting all confused about formulas and such!


  VoG™ 19:29 18 Feb 2003

You need to use either the VLOOKUP worksheet function od a combination of INDEX and MATCH - I think the latter if you want to put something to the left of the column that you are searching.

As an example of the formula construction:


Sorry, I have to logoff now; back later. Hopefully this will get you started. Look these functions up in Help.

  recap 19:39 18 Feb 2003

no problem, I thought you might like to get your teeth into this one :-)

regards, recap

  VoG™ 22:36 18 Feb 2003

Having looked again at this problem I cannot see a way of solving it easily using the standard worksheet functions. Therefore the way forward is to create two UDFs (User Defined Functions).

ALT+F11 to enter the Visual Basic Editor

Insert/Module and paste in the following:

Function LK1()


Dim iRow As Integer

For iRow = 5 To 50

If ActiveSheet.Cells(iRow, 6) < 0 Then Exit

Next iRow

LK1 = ActiveSheet.Cells(iRow, 6).Value

End Function

Function LK2()


Dim iRow As Integer

For iRow = 5 To 50

If ActiveSheet.Cells(iRow, 6) < 0 Then Exit For

Next iRow

LK2 = ActiveSheet.Cells(iRow, 2).Value

End Function

Now ALT+F11 to exit the VBE.

In D1 enter the formula


and in D2 enter the formula


Note: there is no error checking - if no negative value is found you will end up with the values corresponding to F50.

  Kypros 11:06 19 Feb 2003

Thanks, but I found that a little too complicated (I am not very good with these things). I got two good answers from Mr Excel from Aladin Akyurek and Phamtom1975 which were just about OK for me to grasp.

click here

  VoG™ 11:45 19 Feb 2003

Aladin's solution is the best of those two.

I knew there was a way using INDEX and MATCH but couldn't quite get my head around it. I jumped on the VBA wagon, just like Phantom1975.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone