Search for specific text within last non blank cell in a range

B

Bevo

Hi

In MS Excel 2007, I'm trying to use a function to search for (and display) a
specific text string that resides within a cell, providing that it is the
last non blank cell in a range of cells across a row.

e.g. If searching row 2:2 (or a section of this row) for the last non blank
cell, I want to then search within that last cell to locate the word "pass",
where the same cell may also contain any combination of text/number values.
If "pass" exists within the cell, I want to return a the same text value of
"pass". Note that “pass†may exist across various cells in the range.

-- I've managed to successfully return the entire contents that reside
within the last non-blank cell (with the following formula):

=IF(ISNA(LOOKUP(2,1/('QA Results'!$D2:$CY2<>""),'QA
Results'!$D2:$CY2)),"",LOOKUP(2,1/('QA Results'!$D2:$CY2<>""),'QA
Results'!$D2:$CY2))

-- I've also achieved similar results when searching the cell range for the
last known cell that successfully contains the text “pass†(this is not
necessarily the last non blank cell in the range). Again the entire cell
content is returned.

=IF(OR(ISNA('QA Results'!D2:CY2),ISERROR(LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))),"",LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))

Any assistance would be appreciated.
 
S

Shane Devenshire

If I understand the question then

=IF(ISNUMBER(FIND("pass",LOOKUP(2,1/(B4:L4<>""),B4:L4))),"pass","")

would check the range B4:L4.
 
B

Bevo

Appreciate the feedback Shane

What you've outlined makes perfect sense -- and is what I was after.

Cheers

Bevo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top