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]
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]