Complicated If/Vloolup problem (may be VBA)

I am trying to work out the formula required to identify the Purchase Order number a customer will use, based on the Customer Number and, if a specific condition is met, the Item Number.


The customers in question have provided P/O numbers in advance. The customers fall into one of two categories for Purchase Order classification: 'S' (the same P/O number for all items) or 'M' (P/O numbers are specific to the item number).


I am trying to assign the P/O number to a customer/item combination (one per row), in a worksheet that contains all items ordered that day. This worksheet is then loaded to an ERP system.


I have three worksheets:


'CSVIn' (shows all of the order lines with the following relevant columns: 'Customer Code', 'Item Code', 'Quantity', 'Customer P/O No')


'PO_Types' (a list of customer codes and the type of Purchase Order number system they use: 'S'='Same P/O number for all items' (shown here against the customer code);
'M'='P/O number is item specific'). The columns here are 'Customer Code'; 'PO Type'; 'PO Number'. The 'PO Numbe'r will only be completed if the 'PO Type'='S'.


'Item_PO' (a list of items that have specific P/O numbers - used where the PO_Type for a specific customer is 'S', ie the same number for all items)


I cannot get my'head around the formula needed for this in worksheet 'CSVIn', Column 'CustPO' (where the customer's P/O for an item is stored ready for upload to the ERP system).


The functionality, as I see it is:


- In worksheet 'CSVIn', the formula in column D ('CusPO') needs to take the 'CustCode' in column A and look it up in column A ('Customer Code') of worksheet 'PO_Types'.
- It will then examine the value of column C ('PO Type') of worksheet 'PO_Types' and take one of three actions
1. If the customer code does not exist in worksheet 'PO_Types', it returns a null value (ie, "")
2. If the customer exists and column C ('PO Type') of worksheet PO_Types contains 'S' (single P/O number for all items), it returns the value of column D ('PO_Number')
3. If column C ('PO Type') of worksheet 'PO_Types' contains 'M' (P/O number depends on item number), it takes the values in column A ('Customer Code') and column B ('ItemCode') of worksheet 'CSVIn', and looks them up in worksheet 'Item_PO' (where the 'Customer Code' should appear in column A, and the 'Item Code' in column B), and returns the value in column C ('CurrentPO'). If there is no lookup match, it will return a null ("").

Example of worksheet 'CSVIn'

CustCode ItemCode Quantity CustPO
C10092 141144 4
C10092 0008 5
C10092 91-208 5
C10092 S2327-425-961 5
C10058 3003015 2
C10058 2604447002 2
C10058 4304450 1
C10058 02503556 1

Example of worksheet 'PO_Types'

Customer Code Customer Name PO Type PO Number
C10058 CustA S PO_2016
C10092 CustB M

Example of worksheet 'Item_PO'

Customer Code Item Code CurrentPO
C10092 141144 5689
C10092 0008 5689
C10092 91-208 5689
C10092 S2327-425-961 3985
C10092 82-1074-002 3985

Can anyone help with this, or am I over complicating it, or should it be done by VBA (in which case what would the code be?)?

Many thanks

Hi Bernie

I have just tried this and it looks like it works brilliantly! I will test it further tomorrow.

Many thanks.