Help with Index Custom Function

[david000]

[vba]
Function xIndex(arr1, arr2, arr3)

With Application.WorksheetFunction

xIndex = .Index(arr1, .Match(arr2, arr3, 0))
End With

'I need an additional INDEX but with a missing ROW argument!
'works on the sheet not in the code???

'=xindex($D$2:$D$3,K2,INDEX(E2:F3,,2))
End Function

[/vba]

I really really want to add an additional Index into this Custom Function, but I can't get around the required ROW argument for index in VB where it's not required in Excel (at least you can skip it!)?!

Or ANYTHING that allows me to choose the column dosen't have to be INDEX.



I'm sure someone has this answer in thier trick bag! So, give it up please!

Thanx!

[Bob Phillips]

Sorry David, I am not getting it. The XINDEX function in the ws returns a result, so what exactly are you trying to add?

[david000]

[vba]
Function xIndex2(arr1, arr2, arr3, arr4)

With Application.WorksheetFunction

xIndex2 = .Index(arr1, .Match(arr2, .Index(arr3, , arr4)), 0) 'arr4 selects the column.
End With

'I'm trying to do this formula below. I get an "Arugument Not Optional Error" -
'When I Skip the ROW Arrgument in place of the Column one.
'=INDEX(D23,MATCH(I2,INDEX(E2:F3,,1),0))< Empty Row Argument Causing ERROR. In the VB Only...Not the Worksheet

'I need an additional INDEX but with a missing ROW argument!
'works on the sheet not in the code???
End Function


[/vba]

This link Example3 is what I'm mainly trying to do.

Link >>> contextures.com/xlFunctions03.html#IndexMatch2


I'm really not a huge fan of SUMPRODUCT, because, I can never remember when to use that "--" operator!!! At least with, "Index - text" , "Match- number", you know what to expect.

Exept in this case of course!

I've been over thinking this dumb issue too long.


I'm posting a new sample it's as complete as I can make it...

I'm really sorry about any confusion I may have casued - I'm dumb


Attachment 6156< New Example!!!!

[Bob Phillips]

[vba]

Function xIndex2(arr1, arr2, arr3, arr4)
With Application.WorksheetFunction
On Error Resume Next
xIndex2 = .Index(arr1, .Match(arr2, .Index(arr3, 0, arr4), 0))
On Error GoTo 0
If IsEmpty(xIndex2) Then xIndex2 = ""
End With
End Function
[/vba]

[david000]

Thank you,

I could not figure that out!

I?m so grateful for your help - I just got so frustrated


I thought I was going CRAZY!