Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
|
|||
|
|||
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:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in column, return value in same position of other colum | Excel Worksheet Functions | |||
A function to find a phrase within a range of cells and return the address of the first instance. | Excel Programming | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Check data on colum A and find match on colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) |