• 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 using array as looked up value - Google Sheets to O365Migration

Fábio Negreiros

Fábio Negreiros

New Member
Hello Everyone,

I've being trying for the last two days to find an alternate solution to this problem.

The formula itselft is quite simple:
On Google Sheet:
IFERROR(JOIN(CHAR(10), ArrayFormula(VLOOKUP(SPLIT($G2, ", ", FALSE), 'NIST 800-53r4'!$B$2:$F$1681,3, FALSE ))))

The above formula receives a cell fill with values separated by a comma like "CP-06:00, CP-07:00, CP-08:00" and as a result I receive a array from the informed id's
"ALTERNATE STORAGE SITE"
"ALTERNATE PROCESSING SITE"
"TELECOMMUNICATIONS"

I was able to create a solution using a custom vba function in under 10 minutes, however the customer plans to use excel online, and unfortunately it's not possible to run VBA code online yet.

If anyone need the VBA function I developed, follows below:

Code: 
Function Find_NIST(strNist As String) As String
  Dim wb As Workbook
  Dim sht As Worksheet
  Dim shtData As Worksheet
  Dim rng As Range
  Dim strAux() As String
  Dim strResult As String
  
  Set wb = Application.ActiveWorkbook
  Set sht = wb.ActiveSheet
  Set shtData = wb.Sheets("NIST-CSF")
  Set rng = shtData.Range("tab_NIST")
  
  strAux = Split(strNist, ",")
  For i = 0 To UBound(strAux)
  Do While j <= rng.Rows.Count
  If rng.Cells(j, 4) = Trim(strAux(i)) Then
  strResult = strResult & rng.Cells(j, 5) & Chr(10)
  Exit Do
  End If
  j = j + 1
  Loop
  j = 1
  Next
  Find_NIST = strResult
End Function

Any thoughts?

Regards,

Fábio Negreiros
 
You must log in or register to reply here.
Back
Top