Hi -
I've got about 30 columns of data spanning many rows. Each row out of the 30 column contains cells that say either 'active' or 'lost'. In the 31st column I want a formula that will look back across the 30 columns and if there is at least 1 instance of 'lost' in a cell to return the value of a different specific cell. I think this is a basic array formula, but I am not good with those. Any help is greatly appreciated! Thanks!
jason
Last edited by VBA Noob; 07-21-2008 at 11:20 AM.
Something like
=If(Isnumber(Match("lost",A1:AD1,0)),"lost exists","")
where A1:AD1 is the range to check and "lost exists" is the phrase to return if the word is found in the range... you can replace that with a cell reference if desired.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
works great! Thanks!
follow up question
NBVC,
one additional thing...my first row is a header row with dates (months)...in the final column (after I identified that a 'lost' exists...I want it to look back, if there is a 'lost', return the month from the header row.
Thanks again - your help is really appreciated.
This formula in row 2 will give the column header for the first instance of "lost". Copy down column
=INDEX(A$1:AD$1,MATCH("lost",A2:AD2,0))
Something like:
=If(Isnumber(Match("lost",A2:AD2,0)),Index($A$1:$AD$1,Match("lost",A2:AD2,0)),"")
works!
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks