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 mult...
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.
I could probably get access to Power Query. I currently use Microsoft Power BI which I believe should work the same.
I have attached the sample file for your use.
