Does anyone Know how to make VLOOKUP&IF work together

[bloodmilksky]

Order Date	Customer Order Number	Order Number	Position Number	Item	SKU	Order Quantity (Packs)	Comments
21/12/2015	2000145943	708817101	133	LCLARCT387-0375125160	-0375125160	1	1/1/1970 12:00:00 AM

<tbody>
</tbody>

• So basically I have set up VLOOKUP in the comments column to return a date for the item in the item column.

• The table array I have pulled from another application and is on tab 2.

• The problem I am having is that I have 2x item codes that I need to have the return value in the comments section not as 1/1/1970 12:00:00 but as “ Made To Order”, these item codes are LCLARCX387 & LFQXLXR387

• I managed to come up with this =IF(OR(ISNUMBER(FIND({"LPCMNXR387+0175+300Z17","LCLARCX*","LFQXLXR*"},E12))),"MTO","AWAITING") but It wont intergrate into the VLOOKUP formula(=VLOOKUP(E2,LENS,2,FALSE))

• It should look like this if it works

Order Date	Customer Order Number	Order Number	Position Number	Item	SKU	Order Quantity (Packs)	Comments
25/01/2016	2000146725	708998930	1	LCLARCX387+0025325025	+0025325025	1	Made to Order

<tbody>
</tbody>

 

[Sinon]

Why is the result "Made to order" and not the date? It's not clear. Can you post a sample source data from wherever your vlookup is looking? Also sample of expected results.

 

[bloodmilksky]

Why is the result "Made to order" and not the date? It's not clear. Can you post a sample source data from wherever your vlookup is looking? Also sample of expected results.

my apologies so the Made To Order value should show when there is a "xr" or "rcx" and without these values it should be showing the date.



So VLOOKUP is pulling the information form sheet 2 but I have used name referencing "products"

LFBXLXR387-17845600540	1/1/1970 12:00:00 AM
LCOLRCX387+1234567891	1/1/1970 12:00:00 AM
LCLARCT387+08546821364	12/28/2015 6:21:31 PM
LBLACDT387+74285468742	1/4/2016 2:09:33 PM

<tbody>
</tbody><colgroup><col><col></colgroup>

but instead of the date for the items with xr in them I would like it to say MTO.

LFBXLXR387-17845600540	Made To Order
LCOLRCX387+1234567891	Made To Order
LCLARCT387+08546821364	12/28/2015 6:21:31 PM
LBLACDT387+74285468742	1/4/2016 2:09:33 PM

<tbody>
</tbody>

 

[Sinon]

In that case, try the SEARCH function. For instance, if your item number is in E2, try something like this:
=IF(OR(ISNUMBER(SEARCH("rcx",E2)),ISNUMBER(SEARCH("xr",E2))),"Made to order",VLOOKUP(E2,LENS,2,FALSE))

I added your VLOOKUP at the end according to your first post

 

[bloodmilksky]

Thank you so much Sinon absolute life saver