CODEWITHSUNDEEP
excelexcel-formula
Matt
Matt

Reputation: 11

Excel: Return value to the left of last matching string before a blank cell

I have a list of repeated text strings, and I'm trying to find a way to return the last instance that item before a blank cell and return its corresponding date. There may be instances of that string after the blank, but I'm only interested in returning the date field for the iteration before the first blank cell.

So from the data set linked below, if I am searching for Green, it would return 12/7/2018; Red, it would return 12/2/18; Blue, it would return 12/5/18; Yellow, it would return 12/4/2018

I do not have to accommodate the situation in which there is a blank before the first instance...there will always be data before the initial blank, so returning null is fine...

Sample Data

I've tried a handful of Index/Match combinations, but getting stuck on the Blank cell condition, to then back up to the last matching cell, and returning a value to it's left. Am I thinking about this incorrectly? Should I be starting with searching out the first blank, then searching up for a string from there? Unfortunately, my excel knowledge is limited to what I need to learn to do the job at hand, and this one is vexing me. I'm very vexed.

Upvotes: 0

Views: 120

Answers (2)

MD AZAD HUSSAIN
MD AZAD HUSSAIN

Reputation: 212

A simplified formula

=LOOKUP(2,1/(INDIRECT("B2:B"&MATCH(TRUE,ISBLANK(B2:B11),0))="APPLE"),A2:A11)

Make sure you enter it with CTRL+SHIFT+ENTER so it looks like in the attached output.

enter image description here

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Try this:

=LOOKUP(2,1/($C$1:INDEX(C:C,MATCH(TRUE,INDEX($C$1:$C$100="",0),0)-1)=E3),B:B)

enter image description here

Upvotes: 1

Related Questions