Is there a way I can get excel to vlookup data, pull through the values but only show the negative ones all in one cell. Like having an IF statement looking at the results of the vlookup but all in one formula?

Cheers

here is what I’d do, on a new / hidden sheet do a nested vlookup this =if(vlookup(a1,a2:b6,2)>0,0,(vlookup(a1,a2:b6,2)), that will pull all the negatives

then sum the whole range all in a seperate cell

“only show the negative ones all in one cell”

explain?

As a list? “-4, -5, -24, -54”
or a sum? “-87”

or what?

Sounds like you might need to write a custom vba function to read in the range and loop through each value in the range, append each negative value found into a string variable and return that variable.

something like

Function Negatives (ParamArray arglist() as Variant) as String

Dim cell As Range

Negatives = “”

For Each arg In arglist
For Each cell In arg
If cell < 0 Then Negatives = Negatives & “,” & cell
Next Cell
Next arg

End Function

In your spreadsheet use this function like any other:

=Negatives(A1:A100)

Should also work with multiple ranges : eg =Negatives(A1:A100,B1:B50)

Just tested it and it works fine. I can send it to you if you like?

I think I’ve done it..

If vlookup…..<0,vlookup…….,0

Cheers
Steve

Cleaner version:

Function Negatives(ParamArray arglist() As Variant) As String

Dim arg As Variant

Dim cell As Range

Negatives = “”

For Each arg In arglist
For Each cell In arg
If cell < 0 Then
If Negatives = “” Then
Negatives = cell
Else
Negatives = Negatives & “,” & cell
End If
End If

Next cell
Next arg

End Function

exctly what I was thinking steve-g. No need to get into vb unless completely necessary in my opinion!