Find last cell value in column A where column B value is "Some value"

[coasterman]

Please Login or Register  to view this content.

Hi,

I have an Excel 2007 table that records bank account transactions for several accounts.

I am trying to return the account balance for the most recent transaction for each of the accounts. There is no time stamp with the transactions only a date so many will have the same most recent date. (there is no sorting on the table so most recent transactions are added to the bottom of the table if that makes any difference)

I guess what I need as the question asks to look down column A containing bank account name and find the last instance of "Some account name" then return the value in the same row in column B.

I am new to 2007 and tables so possibly there may be an alternative to this utilising the table object itself?. I need to be conscious of CPU overhead so if I can avoid an array formula to do this job all the better.

Many thanks

[Tony Valko]

Try this...


Data Range

	A	B	C	D	E
1	Acct	Balance	-----	Acct	Balance
2	3	92		3	50
3	4	5
4	2	84
5	5	71
6	1	71
7	1	48
8	4	49
9	3	51
10	3	21
11	1	37
12	3	50
13	5	40
14	2	87
15	2	45

This formula entered in E2:

=LOOKUP(2,1/(A2:A15=D2),B2:B15)

[coasterman]

Thank you, works perfect. Even better as I realise with table object in 2007 I can just use the structured reference without having to bothered about an =offset dynamic range.

=LOOKUP(2,1/(ACC1Table[[#All],[Acc Name]]=E2),ACC1Table[[#All],[ Balance]])

Thanks again

[Tony Valko]

You're welcome. Thanks for the feedback!