PDA

View Full Version : If and Index and Match against a couple of different conditions



ashleyuk1984
04-17-2018, 04:49 AM
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
04-17-2018, 07:47 AM
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
04-17-2018, 03:25 PM
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 :(