'Begins with' style condition in array formula
Hi,
I'm trying to get an array formula to work, but I need help. This is what I've got so far...
=MAX(IF(LEFT(G3:G15,LEN(G3))=G3,O3:O15))
Column G has a set of WBS/Project Outline Numbers in the format 1.2.3.4
Column O has a set of dates
I'm trying to get the maximum date from column O where the value in column G for a row starts with the current rows outline number. At the moment the formula always returns zero.
Does anyone have any other ideas about how I might make a formula to do what I want (perhaps using/row/offset)?
Try this version
=MAX(IF(LEFT(G3:G15)=LEFT(G3),O3:O15))
confirmed with CTRL+SHIFT+ENTER
that works assuming you have single digits only....or do you have values like 12.3.4 where you want to get the max of everything that begins with 12 (everything before the .) if so then change to
=MAX(IF(ISNUMBER(FIND(".",G3:G15)),IF(LEFT(G3:G15,FIND(".",G3:G15))=LEFT(G3,FIND(".",G3)),O3:O15)))
Last edited by daddylonglegs; 02-14-2011 at 04:56 PM.
Audere est facere
Thanks ddl. I ended up taking a different route so that I could be depth agnostic.
The final result of what I was working on is here:http://www.excelforum.com/excel-prog...ml#post2472780
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks