• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup or index match formula based on cell merge text

Ateeb Ali

Member
Dear Sir
One file attached as example, need help in formula.
I have a data with merge value for example "123 - 124 - 125 - 126 - 127" in one cell
the common thing is " - " space and dash in all cells in between numbers
I need formula where if I wan search record against number 125, it should search in the cell where 125 exist and show me result from record column.
Appreciate someone help here, thanks
 

Attachments

  • Chandoo formula.xlsx
    8.9 KB · Views: 11
I don't have a Formula solution for you, but here is a VBA solution.

Code:
Option Explicit

Sub FindNr()
    Dim lrA As Long, lrD As Long, i As Long, j As Long
    Application.ScreenUpdating = False
    lrA = Range("A" & Rows.Count).End(xlUp).Row
    lrD = Range("D" & Rows.Count).End(xlUp).Row
    For i = 3 To lrD
        For j = 3 To lrA
            If InStr(Range("A" & j), Range("D" & i)) > 0 Then
                Range("E" & i) = Range("B" & j)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True

End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button
 
Do you only require the first record where there are multiple matches?
Can you guarantee that one number is never found as a substring within another?
Are you using Office 365 {you may have FILTER and SORT available)?
 

Attachments

  • Lookup concatenated data.xlsx
    13.5 KB · Views: 8
Really interesting formula Sir. Can you please explain why you have taken lookup value as 1. What is the use of having 1 here.
 
If I'm reading Bosco's formula correctly, the key is the minus sign in front of find, as it will return negative values where the criteria matches and errors where the criteria is not found. Looking up 1, will therefore always return the highest (last) match.
From the inner help:
If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
 
Dear Sir
The formula works well on same sheet but I want to bring this formula to another where it is not working, please see attached example, my original formula is below;

=+IF(A2="","",LOOKUP(1,-FIND(A2,'Invoice Data'!$E$2:$E$468,'Invoice Data'!$A$2:$A$468)))
 

Attachments

  • Chandoo formula.xlsx
    9.8 KB · Views: 8
Please change your formula from this >>

=IF(D3="","",LOOKUP(1,-FIND(D3,Sheet1!A3:A7,Sheet1!B3:B7)))

Into this >>

=IF(D3="","",LOOKUP(1,-FIND(D3,Sheet1!A3:A7),Sheet1!B3:B7))

Regards
Bosco
 
If I'm reading Bosco's formula correctly, the key is the minus sign in front of find, as it will return negative values where the criteria matches and errors where the criteria is not found. Looking up 1, will therefore always return the highest (last) match.
From the inner help:
If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
In addition to GraH - Guido's explanation.

This formula logic is using Bignum, and the original writing is :

=LOOKUP(9.9999999999999999E+307,FIND(D3,A$3:A$7),B$3:B$7)

which in the same logic is simplified to >>

=LOOKUP(1,-FIND(D3,A$3:A$7),B$3:B$7)

And, 9.9999999999999999E+307 is the biggest number in Excel, this number is called Bignum.
Of which will return the last number in the range, for example :

=LOOKUP(1,-FIND(D3,A$3:A$7),B$3:B$7)

>> become :

=LOOKUP(1,-FIND(130,{"130 - 131 ";"130 - 131 ";"132 - 133";"132 - 133";"134 - 135 - 136 - 137"}),{"abc";"abc";"xyz";"xyz";"aaa"})

>> become :

=LOOKUP(1,{-1;-1;#VALUE!;#VALUE!;#VALUE!},{"abc";"abc";"xyz";"xyz";"aaa"})

>> become:

="abc"

For more details regarding "return the last number in the range" , please Google in search for "Bignum"

Regards
Bosco
 
A bit out on a limb but, since my interest in Excel is limited to 'How DA changes Excel solutions', I will persevere.
First I used a defined Name 'isPresent' to hold an intermediate formula
= ISNUMBER( SEARCH(Result[@Number], Table[Number] ) )
I do that to encapsulate the calculation because the character location of the match is of no direct relevance to the solution. That done, I have used the new function XLOOKUP to determine whether any matches were found.
= XLOOKUP( TRUE, isPresent, Table[Record], "zzz", 0,-1)
Features of note:
The 0 (default) requires and exact match of the Boolean value TRUE
The -1 requires the lookup to return the last location in the event of multiple matches
The "zzz" is returned if TRUE is not found.
A slight disadvantage of the name approach is that, if a further list were to be returned for a second table, a new name would need to be defined to be used in place of 'isPresent' which is specific to the particular results table.
 

Attachments

  • Lookup concatenated data.xlsx
    20.6 KB · Views: 4
Back
Top