Convert vlookup to more than 255 characters via Excel VBA

Question

I am looking for reverse vlookup with more than 255 characters in Excel VBA. This is the formula based one which I took from this website.

=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))

I have try to convert it in VBA. Here below sample code

Sub test()

'concat
Range("i1") = WorksheetFunction.TextJoin(" ", True, Range("g1:h1"))

'lookup
Sal1 = Application.WorksheetFunction.Index(Sheets("sheet1").Range("a1:a2"), Application.WorksheetFunction.Match(True, Application.WorksheetFunction.Index(Sheets("sheet1").Range("i1:i1") = Range("i1").Value, 0), 0))
'=INDEX($W$3:$W$162,MATCH(TRUE,INDEX($W$3:$W$162=U3,0),0))


End Sub

It works well but it didn't when i change the range("i1:i1") to range("i1:i2")

Answer 1

Expressing matches via VBA

I like to know if there (are) any possibilities to convert this formula.

=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))

So "reverse VLookUp" in title simply meant to express the (single) formula result via VBA (btw I sticked to the cell references in OP, as you mention different range addresses in comments).

This can be done by simple evaluation to give you a starting idea:

'0) define formula string
    Dim BaseFormula As String
    BaseFormula = "=INDEX($F$2:$F$10,MATCH(TRUE,INDEX($D$2:$D$10=$A2,0),0))"

'1) display single result in VB Editor's immediate
    Dim result
    result = Evaluate(BaseFormula)
    Debug.Print IIf(IsError(result), "Not found!", result)

On the other hand it seems that you have the intention to extend the search string range from A2 to more inputs (e.g. till cell A4). The base formula wouldn't return a results array with this formula, but you could procede as follows by copying the start formula over e.g. 3 rows (note the relative address ...=$A2 to allow a row incremention in the next rows):

'0) define formula string
    Dim BaseFormula As String
    BaseFormula = "=INDEX($F$2:$F$10,MATCH(TRUE,INDEX($D$2:$D$10=$A1,0),0))"

'2) write result(s) to any (starting) target cell
    'a)Enter formulae extending search cells over e.g. 3 rows (i.e. from $A2 to $A4)
    Sheet3.Range("H2").Resize(3).Formula2 = BaseFormula
    'b) optional overwriting all formulae, if you prefer values instead
    'Sheet3.Range("H2").Resize(3).Value = Tabelle3.Range("G14").Resize(3).Value

Of course you can modify the formula string by any dynamic replacements (e.g. via property .Address(True,True,External:=True) applied to some predefined ranges to obtain absolute fully qualified references in this example).

Some explanations to the used formulae

The formula in the cited link

=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))

describes a way to avoid an inevitable #NA error when matching strings with more than 255 characters directly.

Basically it is "looking up A2 in D2:D10 and returning a result from F2:F10" similar to the (failing) direct approach in such cases:

=INDEX(F2:F11,MATCH(A2,D2:D11,FALSE))

The trick is to offer a set of True|False elements (INDEX(D2:D10=A2,0)) which can be matched eventually without problems for an occurence of True.

Full power by Excel/MS 365

If, however you dispose of Excel/MS 365 you might even use the following much simpler function instead and profit from the dynamic display of results in a so called spill range. That means that matches can be based not only on one search string, but on several ones (e.g. A1:A2), what seems to solve your additional issue (c.f. last sentence in OP) to extend the the search range as well.

=XLOOKUP(A1:A2,D2:D10,F2:F10,"Not found")

Answer 2

    Function betterSearch(searchCell, A As Range, B As Range)
            For Each cell In A
                If cell.Value = searchCell Then
                        betterSearch = B.Cells(cell.Row, 1)
                        Exit For
                End If
                betterSearch = "Not found"
            Next

End Function

i found this code from above link and it is useful for my current search.Below examples i try to get value..

Kindly consider Row 1 to 5 as empty for A and B column because my table always start from Row 6

Row	A Column	B Column
6	54	a
7	55	b
8	56	c

VBA Code:

Sub look_up ()
Ref = "b"
look_up = betterSearch(Ref, Range("B6:B8"), Range("A6:A8"))
End Sub

it show Empty while use Range("B6:B8"), Range("A6:A8")

but when changing the range from B6 and A6 to B1 and A1 (Range("B1:B8"), Range("A1:A8") )it gives the value...

My question is "can get the values from desired range"

Answer 3

I'm not sure what that worksheet formula does that =INDEX(F2:F11,MATCH(A2,D2:D11,FALSE)) doesn't do.

This part Index(Sheets("sheet1").Range("i1:i2") = Range("i1").Value, 0) is comparing a 2-d array to a single value, which should result in a Type Mismatch error. Whenever you reference a multi-cell range's Value property (Value is the default property in this context), you get a 2-d array even if the range is a single column or row.

You could fix that problem with Application.WorksheetFunction.Transpose(Range("D1:D10")) to turn it into a 1-d array, but I still don't think you can compare a 1-d array to a single value and have it return something that's suitable for passing into INDEX.

You could use VBA to create the array's of Trues and Falses, but if you're going to go to that trouble, you should just use VBA to do the whole thing and ditch the WorksheetFunction approach.

I couldn't get it to work when comparing a single cell to a single cell like you said it did.

Here's one way to reproduce the formula

Public Sub test()
    
    Dim rFound As Range
    
    'find A2 in D
    Set rFound = Sheet1.Range("D1:D10").Find(Sheet1.Range("A2").Value, , xlValues, xlWhole)
    
    If Not rFound Is Nothing Then
        MsgBox rFound.Offset(0, 2).Value 'read column f - same position as d
    End If
    
End Sub

If that simpler formula works and you want to use WorksheetFunction, it would look like this

Public Sub test2()
    
    Dim wf As WorksheetFunction
    
    Set wf = Application.WorksheetFunction
    
    MsgBox wf.Index(Sheet1.Range("F2:F11"), wf.Match(Sheet1.Range("A2").Value, Sheet1.Range("D2:D11"), False))
    
End Sub