If and Index and Match against a couple of different conditions

[ashleyuk1984]

Hi,
I'm attempting - what should be a fairly straight forward nested IF with index and match.
However, I'm coming up with a few issues... It seems like I've totally forgotten how to do this effectively.

I've attached an excel document, which hopefully explains the problem as well as possible.

There are two main issues that I have with the data set that we store, and the one that's provided to me.

The cells store multiple PO numbers. And I'm trying to match just one of them, and retreive data from another column (if it's found).
The next problem is that the data set that I'm trying to match from, splits the PO's into two cells.

So our PO's may look like either 123456-20 or 123456 (without a trailing hyphen and number).

I think the excel document explains this better, as you will see the data that I'm trying to work with.

I've also placed the formulas that I'm trying to create in column H.

Because the PO's can be anywhere in the cell, I'm using wildcards (but this is causing my headache).

Thanks for your help.

[Paul_Hossler]

Can you use a VBA user defined function?

Option Explicit

Sub drv()
    With ActiveSheet
        MsgBox FindPOData(Range("E3"), Range("F3"), Range("B:C"))
        MsgBox FindPOData(Range("E4"), Range("F4"), Range("B:C"))
        MsgBox FindPOData(Range("E5"), Range("F5"), Range("B:C"))
    End With
End Sub
 

Function FindPOData(PO1 As String, PO2 As String, POlist As Range, Optional IfNotFound As String = "PO Not Found") As Variant
    Dim PO As Range
    
    Set PO = POlist.Columns(1).Find(PO1 & "-" & PO2, POlist.Cells(1, 1), xlValues, xlPart)
    
    If PO Is Nothing Then
        Set PO = POlist.Columns(1).Find(PO1 & PO2, POlist.Cells(1, 1), xlValues, xlPart)
    End If
    
    If PO Is Nothing Then
        FindPOData = IfNotFound
    Else
        FindPOData = PO.Offset(0, 1).Value
    End If
End Function

[ashleyuk1984]

Hi Paul,

I wish I could, but unfortunately I can't.
My spreadsheet is actually on Google Sheets, so formulas are ok, but no VBA