Forum Discussion
Get a value in column to the left of the last entry in a row, if last entry equals 'Pending'
I have raw data that reports multiple responses on a specific document. Each document has its own row, and the responses are located in a specific column of that row. The responses cell includes multiple lines of text delimited by ENTER. I have used the text to columns data tool to split this column into multiple columns (See photo for reference).
I then used the following formula to return the last non-blank value in the row =LOOKUP(2,1/(Sheet1!2:2<>""),Sheet1!2:2). My issue is, if this function returns the value "Pending", I want it to return the next value (to the left). If the value to the left is also "Pending", return the next value until the value returned does not equal "Pending".
5 Replies
Hello
=LOOKUP(2,1/(SUBSTITUTE(Sheet1!2:2,"Pending","")<>""),Sheet1!2:2)
This worked perfectly! Thank you!
I also have a follow-up question:
Is there a way to achieve a similar result, without using the text to columns function? The below photo shows how my raw data is formatted (separate lines delimited by ENTER).
In a 3rd column i want to return the last data line in column A, that does not equal 'Pending'.
In a 4th column i want to return the data line in column B that corresponds to the same data line returned from Column A.
It could be possible with Power Query. Is Power Query available? If yes then provide a sample file.
