Find Last instance colum A and return corresponding B Value
Hi
I have two columns of data one with numbers (columnA) and the other with Y or N (column b) What I'd like to do is find the last instance of a number in column A and return the corresponding B value. Column A value can appear any number of times. e.g Column A Column B 100 N 101 N 101 Y 102 N 103 N 103 N 103 Y 104 N 104 Y Essentially for each of 100,101,102 etc I want to look at the last instance of it in column A and return to my secified cell what the corresponding B value is All help appreciated. Thanks Will. |
Find Last instance colum A and return corresponding B Value
Hi Will,
Am Tue, 2 Jul 2013 12:29:17 +0100 schrieb Will3001: Column A Column B 100 N 101 N 101 Y 102 N 103 N 103 N 103 Y 104 N 104 Y for 100 try: =INDEX($B$1:$B$100,LOOKUP(2,1/($A$1:$A$100=100),ROW($1:$100))) you also can change the value with a cell reference Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Hi Claus,
That appears to do the job nicely, thank you, will try it on a larger data set later on.. Thanks again Will. Quote:
|
All times are GMT +1. The time now is 02:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com