If Statement and Vlookups with Specific Text (Mult Specific Text)

srsev6

New Member
Joined
Sep 3, 2013
Messages
21
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Order No

[/TD]
[TD]Order Status
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]Shipped 2/20/19
[/TD]
[/TR]
[TR]
[TD]1235
[/TD]
[TD]Shipped 1/5/19
[/TD]
[/TR]
[TR]
[TD]1236
[/TD]
[TD]Prepped for Shipment
[/TD]
[/TR]
[TR]
[TD]1237
[/TD]
[TD]Shipped 3/1/19
[/TD]
[/TR]
[TR]
[TD]1238
[/TD]
[TD]Shipped 3/10/19
[/TD]
[/TR]
[TR]
[TD]1239
[/TD]
[TD]Shipment Confirmed
[/TD]
[/TR]
[TR]
[TD]1240
[/TD]
[TD]Shipped 2/15/19
[/TD]
[/TR]
[TR]
[TD]1241
[/TD]
[TD]Prepped for Shipment
[/TD]
[/TR]
[TR]
[TD]1242
[/TD]
[TD]Shipment Confirmed
[/TD]
[/TR]
</tbody>[/TABLE]

I would like to do a vlookup for the following:

The lookup the criteria in column A

The range is in column B

If the text in column B is "Shipped" with a date then the result should be X
If the text in column B is "Shipment Confirmed" then the result should be "Shipment Confirmed"
If the text in column B is "Prepped for Shipment" then the result should be "Prepped for Shipment"

If the result doesn't meet any of the criteria above then blank.

So for Order No 1239 and 1242 I would like the result to be "Shipment Confirmed" and for Order No 1241 and 1236 the result would be "Prepped for Shipment". For anything that says "Shipped" with a date should be X. If there is any other text not listed above or a blank in the Order Status the result should be blank.

Thank you all for your time and I look forward to the results.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Like this (substituting lookupvalue)?

=IF(LEFT(VLOOKUP(lookupvalue,A:B,2,0),5)="Shipp","X",VLOOKUP(lookupvalue,A:B,2,0))
 
Upvote 0
=IF(LEFT(VLOOKUP(lookupvalue,A:B,2,0),5)="Shipp","X",VLOOKUP(lookupvalue,A:B,2,0))

My apologies.....the example was a quick easy view. The "Order No" is actually in column A and the "Shipment Status" is actually in column M.

The table array is in another spreadsheet columns A thru AX. The column with the column index number is 21.

I should have explained myself better. I sincerely apologize and thank you for your response.
 
Upvote 0
This actually worked fine except it won't produce the other results-Prepped for Shipment and Shipment Confirmed. I am getting a #N/A for these when it should be Prepped for Shipment and Shipment Confirmed. But the results of Shipped [date] is an X as I wanted.
 
Upvote 0
Getting an N/A on a VLOOKUP indicates the value your looking for doesn't exist in the range you're searching.

You may want to double check the lookup values are there and use =EXACT() to compare the 2 cells to each other. There may be a few reasons cells look the same but aren't
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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