Prefer Formula Solution to lookup value

[Anne Troy]

Suppose I have these two columns, which are the employee and the amount they owe. In columns to the left (not shown below), I'm tracking payroll deductions/advances.

John 600
Judy 300
John 500
Judy 250
Jade 1000
Jade 500
John 300
Judy 600

I want to have another set of columns to show me the current amount owed, like:

John 300
Judy 600
Jade 500

Because the amount they owe will always be the last amount at the bottom of the column. It's not necessarily the lowest amount, like MIN, so how do I get the last amount for a given name?

I would call Bill directly, but he's just so important now. LOL

Thanks in advance!!

 

[ranman256]

the code gets a name then searches from bottom up.

Public Sub Test()
MsgBox WhatDoTheyOwe("John")
End Sub


Function WhatDoTheyOwe(ByVal pvName)
Range("A1").Select
Selection.End(xlDown).Select  'goto bottom
'begin searching from bottom
While ActiveCell.Row > 1
  If pvName = ActiveCell.Value Then
     WhatDoTheyOwe = ActiveCell.Offset(0, 1).Value
     Exit Function
  End If
  
  ActiveCell.Offset(-1, 0).Select  'next row up
Wend
End Function

you should really learn Microsoft Access. It is the prefect tool for doing this sort of thing.

 

[Weazel]

maybe something like...

Unknown

	A	B	C	D	E
2	John	600		John	300
3	Judy	300		Judy	600
4	John	500		Jade	500
5	Judy	250
6	Jade	1000
7	Jade	500
8	John	300
9	Judy	600

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =LOOKUP(2,1/($A$2:$A$9=D2),$B$2:$B$9) <tbody> </tbody>

<tbody>
</tbody>

 

[Anne Troy]

Sorry. I have about 8 employees who get advances. I'm not building a database for it. I have one, but I'm not doing payroll with it.

I'm not sure how I would do this as a formula in multiple cells.

Thanks for your time!

 

[Aladin Akyurek]

Are the names in the output known or must they also be computed?

 

[Anne Troy]

maybe something like...

Unknown

	A	B	C	D	E
2	John	600		John	300
3	Judy	300		Judy	600
4	John	500		Jade	500
5	Judy	250
6	Jade	1000
7	Jade	500
8	John	300
9	Judy	600

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =LOOKUP(2,1/($A$2:$A$9=D2),$B$2:$B$9) <tbody> </tbody>

<tbody>
</tbody>

That's beautiful, Weazel. I didn't even get a chance to give my sample file link. www.annetroy.com/mrexcel/Sample.xlsx

 

[Weazel]

Great, hope it helps

 

[Anne Troy]

Great, hope it helps

Sure does! Perfect.