Searching for a text string in a list and returning a value

I'm making offers on real estate properties and want to avoid making duplicate offers on the same property. I'd like some help searching each cell in green and locating the string of format "MLS # 4653962", then taking this value and searching each cell for the same string in the list in yellow. If found, a "yes" should be placed in the "already offered" column. The space in between the two lists can be used as the marker for new list versus old, rather than using green vs yellow. I realize the data could be delimited using spaces and a VLOOKUP done, but I'd like some help with some code that would be more robust if say, the data in the lists weren't symmetric and couldn't be easily space delimited. Thanks in advance for any help with this.

Hi Panda2012 and welcome to the forum.

I think you can use a CountIf function. See the attached.

Good afternoon Marvin,
Thank you for the quick reply! That would be fine if the data format in the two lists was always identical, but let's just assume that at times the only part of the text in each cell that is always identical , is the "MLS # 4653962" string. Sometimes the databases from which I take the listings won't list the address and MLS# for the same property, in the same format.

Not sure I have this the correct way round.

In B2

Please Login or Register  to view this content.

Drag/Fill down yellow range.

In B21

Please Login or Register  to view this content.

Drag/Fill down yellow range.

Hope this helps.

Hi,

See Dynamic Named Ranges at http://www.beyondtechnology.com/geeks007.shtml
Then use the Name of the range instead of your $B$21....

@ MarvinP

In common with many links to sites that explain Dynamic Lists, that site uses the volatile function OFFSET().

I find it more useful to use the non-volatile function INDEX()

See this workbook
Name:= MLS_Codes
Refers to:=

Please Login or Register  to view this content.

In large worksheets this can make a very real difference to the operation of the workbook.
The volatile method will recalculate every time anything is changed in the workbook.
The price to pay is that the non-volatile method will take longer to open,("quasi-volatile"?), but that's nothing when weighed against the considerable saving in operation time.

@ Panda2012
Better, if the ranges are going to vary in height, to keep them in different columns or sheets.

See the attached.