Does anyone Know how to make VLOOKUP&IF work together

[bloodmilksky]

[TABLE="width: 1125"]
<tbody>[TR]
[TD]Order Date
[/TD]
[TD]Customer Order Number
[/TD]
[TD]Order Number
[/TD]
[TD]Position Number
[/TD]
[TD]Item
[/TD]
[TD]SKU
[/TD]
[TD]Order Quantity (Packs)
[/TD]
[TD]Comments
[/TD]
[/TR]
[TR]
[TD]21/12/2015
[/TD]
[TD]2000145943
[/TD]
[TD]708817101
[/TD]
[TD]133
[/TD]
[TD]LCLARCT387-0375125160
[/TD]
[TD]-0375125160
[/TD]
[TD]1
[/TD]
[TD]1/1/1970 12:00:00 AM
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

• 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

[TABLE="width: 1125"]
<tbody>[TR]
[TD]Order Date
[/TD]
[TD]Customer Order Number
[/TD]
[TD]Order Number
[/TD]
[TD]Position Number
[/TD]
[TD]Item
[/TD]
[TD]SKU
[/TD]
[TD]Order Quantity (Packs)
[/TD]
[TD]Comments
[/TD]
[/TR]
[TR]
[TD]25/01/2016
[/TD]
[TD]2000146725
[/TD]
[TD]708998930
[/TD]
[TD]1
[/TD]
[TD]LCLARCX387+0025325025
[/TD]
[TD]+0025325025
[/TD]
[TD]1
[/TD]
[TD]Made to Order
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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"

[TABLE="width: 379"]
<tbody>[TR]
[TD]LFBXLXR387-17845600540[/TD]
[TD]1/1/1970 12:00:00 AM[/TD]
[/TR]
[TR]
[TD]LCOLRCX387+1234567891[/TD]
[TD]1/1/1970 12:00:00 AM[/TD]
[/TR]
[TR]
[TD]LCLARCT387+08546821364[/TD]
[TD]12/28/2015 6:21:31 PM[/TD]
[/TR]
[TR]
[TD]LBLACDT387+74285468742[/TD]
[TD]1/4/2016 2:09:33 PM
[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

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

[TABLE="width: 379"]
<tbody>[TR]
[TD]LFBXLXR387-17845600540[/TD]
[TD]Made To Order[/TD]
[/TR]
[TR]
[TD]LCOLRCX387+1234567891[/TD]
[TD]Made To Order[/TD]
[/TR]
[TR]
[TD]LCLARCT387+08546821364[/TD]
[TD]12/28/2015 6:21:31 PM[/TD]
[/TR]
[TR]
[TD]LBLACDT387+74285468742[/TD]
[TD]1/4/2016 2:09:33 PM[/TD]
[/TR]
</tbody>[/TABLE]

 

[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