# 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### 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
3
Views
174
Replies
3
Views
66
Replies
7
Views
356
Replies
5
Views
267
Replies
4
Views
234