logo
down
shadow

Vlookup not working with text search


Vlookup not working with text search

By : Vipvipvip Vipvipvip
Date : November 19 2020, 12:41 AM


Share : facebook icon twitter icon
How to optimize vlookup for high search count ? (alternatives to VLOOKUP)

How to optimize vlookup for high search count ? (alternatives to VLOOKUP)


By : NOEL
Date : March 29 2020, 07:55 AM
it should still fix some issue I considered the following alternatives:
VLOOKUP array-formula MATCH / INDEX VBA (using a dictionary)
code :
         A          B
     1
     2   key51359    {=VLOOKUP(A2:A10001;sheet1!$A$2:$B$100001;2;FALSE)}
     3   key41232    formula in B2
     4   key10102    ... extends to
   ...   ...         ... 
 99999   key4153     ... cell B100001
100000   key12818    ... (select whole range, and press
100001   key35032    ... CTRL+SHIFT+ENTER to make it an array formula)
100002
         A           B                                       C
      1
      2  key51359    =MATCH(A2;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B2)
      3  key41232    =MATCH(A3;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B3)
      4  key10102    =MATCH(A4;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B4)
    ...  ...         ...                                     ...
  99999  key4153     =MATCH(A99999;sheet1!$A$2:$A$100001;)   =INDEX(sheet1!$B$2:$B$100001;B99999)
 100000  key12818    =MATCH(A100000;sheet1!$A$2:$A$100001;)  =INDEX(sheet1!$B$2:$B$100001;B100000)
 100001  key35032    =MATCH(A100001;sheet1!$A$2:$A$100001;)  =INDEX(sheet1!$B$2:$B$100001;B100001)
 100002
       A          B
     1
     2  key51359    {=vbalookup(A2:A50001;sheet1!$A$2:$B$100001;2)}
     3  key41232    formula in B2
     4  key10102    ... extends to
   ...  ...         ...
 50000  key91021    ... 
 50001  key42       ... cell B50001
 50002  key21873    {=vbalookup(A50002:A100001;sheet1!$A$2:$B$100001;2)}
 50003  key31415    formula in B50001 extends to
   ...  ...         ...
 99999  key4153     ... cell B100001
100000  key12818    ... (select whole range, and press
100001  key35032    ... CTRL+SHIFT+ENTER to make it an array formula)
100002
Function vbalookup(lookupRange As Range, refRange As Range, dataCol As Long) As Variant
  Dim dict As New Scripting.Dictionary
  Dim myRow As Range
  Dim I As Long, J As Long
  Dim vResults() As Variant

  ' 1. Build a dictionnary
  For Each myRow In refRange.Columns(1).Cells
    ' Append A : B to dictionnary
    dict.Add myRow.Value, myRow.Offset(0, dataCol - 1).Value
  Next myRow

  ' 2. Use it over all lookup data
  ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
  For I = 1 To lookupRange.Rows.Count
    For J = 1 To lookupRange.Columns.Count
      If dict.Exists(lookupRange.Cells(I, J).Value) Then
        vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
      End If
    Next J
  Next I

  vbalookup = vResults
End Function
VlookUp working with Numbers but Not Text

VlookUp working with Numbers but Not Text


By : Jb Rifluxyss
Date : March 29 2020, 07:55 AM
hope this fix your issue I have been using VLookUp for matching numbers I want trying to use it on text with exact match , Change lValue to a variant
code :
Dim lValue As variant
Vlookup Not working on text between two tables

Vlookup Not working on text between two tables


By : Naveen
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , @Jeeped's comment has a good answer:
Assuming you have already trimmed off leading and trailing spaces, one of the John Smith entries (likely the one from the web) uses a non-breaking space (e.e. CHAR(160) or ASCII 0×A0) instead of a regular space (e.g CHAR(32) or ASCII 0×20). Use
code :
=CODE(MID(A$1, ROW(1:1), 1)) 
How to delete found values from Vlookup function but not from excel itself, so the vlookup wont search the cells again

How to delete found values from Vlookup function but not from excel itself, so the vlookup wont search the cells again


By : KMi
Date : March 29 2020, 07:55 AM
may help you . Unless you need the values to dynamically update; the VBA is a better alternative to 100K formulas. Using an ArrayList and arrays it took 1.98 seconds to process the data.
code :
Sub ValidateData()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'You'll need to adjust these const values
    Const LOOKUP_SHEET As String = "newsheet"
    Const TARGET_SHEET As String = "oldsheet"
    Dim x As Long, y As Long
    Dim data As Variant, results As Variant
    Dim key As String
    Dim list As Object
    Set list = CreateObject("System.Collections.ArrayList")

    With Worksheets(LOOKUP_SHEET)
        'Load the values from columns A and B into an array
        data = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 2)
    End With

    For x = 1 To UBound(data, 1)
        'Create a unique identifier
        'using a delimiter to ensure value don't mix
        key = data(x, 1) & "|" & data(x, 2)
        If Not list.Contains(key) Then list.Add key
    Next

    With Worksheets(TARGET_SHEET)
        'Load the values from columns A and B into an array
        data = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 2)
        'Resize the results array
        ReDim results(1 To UBound(data), 1 To 1)

        For x = 1 To UBound(data, 1)
            'Create a unique identifier
            'using a delimiter to ensure value don't mix
            key = data(x, 1) & "|" & data(x, 2)
            results(x, 1) = IIf(list.Contains(key), "Correct", "Wrong")
        Next

        .Range("C1").Resize(UBound(results, 1)) = results
    End With

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub
How do combine formulas to be used for VLOOKUP to search for a part number in a text string?

How do combine formulas to be used for VLOOKUP to search for a part number in a text string?


By : user7104101
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I have a list of part numbers that are combined with their old part numbers so e.g. PN1234/XMS1234, the XMS part number is what I need and the strings are all different lengths and in different positions. So I need VLOOKUP to find the XMS part number and bring me the price from a different spreadsheet that has the part number correctly. , What about this:
code :
=IF(LEFT(A1,3)="XMS",LEFT(A1,SEARCH("/",A1)-1),RIGHT(A1,LEN(A1)-SEARCH("XMS",A1)+1))
Related Posts Related Posts :
  • "Overload" F9 (or shift+F9) in Excel spreadsheet with VBA function?
  • ShowAllData method of Worksheet class failed
  • VBA If cell is under a certain length, highlight and display message
  • VBA: Unable to get the Match property of the WorkSheet function class
  • Highlighting the second and more duplicate rows in excel but not first occurrence?
  • Excel add- in focus not returned to the active sheet
  • How to restrict VBA code to a specific worksheet?
  • How to insert column name in the destination table in ssis?
  • Insert text into the background of a cell
  • how do I generate random timestamps in Excel
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Huge excel file... any advice?
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org