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

[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.

 

[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.

 

[Bevo]

Appreciate the feedback Shane

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

Cheers

Bevo