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.
<tbody>
</tbody>
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.
Column1 | DATE | PORT | |||||
Text43101 | 1/1/2018 | At Sea | |||||
Text43102 | 1/2/2018 | At Sea | |||||
Text43103 | 1/3/2018 | At Sea | |||||
Text43104 | 1/4/2018 | New Orleans, LA, USA |
<tbody>
</tbody>