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

#### srsev6

##### New Member
 Order No Order Status 1234 Shipped 2/20/19 1235 Shipped 1/5/19 1236 Prepped for Shipment 1237 Shipped 3/1/19 1238 Shipped 3/10/19 1239 Shipment Confirmed 1240 Shipped 2/15/19 1241 Prepped for Shipment 1242 Shipment Confirmed

<tbody>
</tbody>

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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### mrshl9898

##### Well-known Member
Like this (substituting lookupvalue)?

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

#### srsev6

##### New Member
=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.

#### srsev6

##### New Member
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.

#### mrshl9898

##### Well-known Member
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

Replies
10
Views
471
Replies
13
Views
134
Replies
3
Views
386
Replies
3
Views
347
Replies
3
Views
2K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,714
Messages
5,833,279
Members
430,201
Latest member
Deepakpilla36

### 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.

### Which adblocker are you using?

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

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