Return different value if certain text exists in lookup cell

Achtig

New Member
Joined
Feb 1, 2018
Messages
4
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]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top