B4J Question Excel Vlookup & Index Match Function

[h.drose]

Hi,

There is any way to use Vlookup and Index - Match Functions on BJ4?

On Visual basic must be like this:

example: Application.WorksheetFunction.Vlookup(x, Sheet3.Range("C:AZ"), 39, 0)

I want do the same, how can I do this?

 

[Erel]

Get the data with XLReader and do whatever you like programmatically.

Dim ReaderResult As XLReaderResult = XL.Reader.ReadSheetByIndex(File.DirAssets, "Countries of the world.xlsx", 0)
Dim range As XLRange = XL.CreateXLRange(XL.AddressName("B6"), XL.AddressName("C232"))
Dim address As XLAddress = FindValueInRange(ReaderResult, range, "Zambia")
If address.IsInitialized Then
    Log($"Population: $1.0{ReaderResult.Get(XL.AddressOne("D", address.Row0Based + 1))}"$)
End If

Private Sub FindValueInRange (ReaderResult As XLReaderResult, Range As XLRange, Value As String) As XLAddress
    For c = Range.FirstAddress.Col0Based To Range.SecondAddress.Col0Based
        For r = Range.FirstAddress.Row0Based To Range.SecondAddress.Row0Based
            Dim address As XLAddress = XL.AddressZero(c, r)
            Dim v As String = ReaderResult.Get(address).As(String).Trim 'remove whitespace
            If v = Value Then Return address
        Next
    Next
    Dim a As XLAddress
    Return a 'uninitialized
End Sub

XLUtils / jPOI 5 - Read and write MS Excel workbooks

As written here, I plan to make it easier to read and write Excel workbooks. The solution is based on three components: - Apache POI - https://poi.apache.org/ Large open source project that provides APIs for Microsoft documents. Note that the files are accessed directly, it doesn't depend on the...

www.b4x.com

 

[h.drose]

Many Thanks Erel !