Return different value if certain text exists in lookup cell

[Achtig]

I put together a Vlookup to check concatenated value based on text and a certain date in two worksheets and return a value based on that. The lookup date is always the first of a month. The column it returns the value from has either "At Sea" or different cities in it. In many cases the value "At Sea" is returned but I need it to show a city. If the value returns "At Sea" I need the formula to check the following cells below and return the first value that has a city.

I added the example it returns the value from below, the concatenated lookup value is in Column1, the number after the text, 43101, represents the date, in this case 1/1/2018 and it returns value from column PORT. Since the value is "At Sea", the formula should now check the cell below and since it is a city, stop and return the value from 1/2/2018, New Orleans, LA, USA.
Any help is much appreciated, I was unable to find any information to solve this problem.


[TABLE="width: 986"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]DATE
[/TD]
[TD]PORT
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Text43101
[/TD]
[TD]1/1/2018
[/TD]
[TD]At Sea
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Text43102
[/TD]
[TD]1/2/2018
[/TD]
[TD]At Sea
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Text43103
[/TD]
[TD]1/3/2018
[/TD]
[TD]At Sea
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Text43104
[/TD]
[TD]1/4/2018
[/TD]
[TD]New Orleans, LA, USA
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

It sounds like you are just looking for the first non "At Sea" value in column C.

=INDEX(C2:C100,MATCH(TRUE,C2:C100<>"At Sea",0))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[Achtig]

Hi Scott,

Thanks for your reply, I tried your suggestion in the below formula, but I get only #VALUE ! in return. Maybe I need to clarify further, I enter the formula in worksheet "CCL", then lookup the value from cell A48 in multiple arrays (2 worksheets), "2018" & "2019". The current lookup formula (=IFNA(VLOOKUP(A49,'2019'!A:I,9,0),VLOOKUP(A49,'2018'!A:I,9,0)), returns "At Sea". So first it need to find the same value in the array, if "At Sea" is found look for the first non "At Sea" in the next cells below. Hopefully this will clarify better?

=INDEX("2019'!A2:I50000&2018'!A2:I50000",MATCH(A48,"2019'!A2:I50000&2018'!A2:I50000"<>"At Sea",0))