Excel vlookup in Julia

Question

I have two arrays in Julia, X = Array{Float64,2} and Y = Array{Float64,2}. I'd like to perform a vlookup as per Excel functionality. I can't seem to find something like this.

Answer 1

VLOOKUP is a popular function amongst Excel users, and has signature:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

I've never much liked that last argument range_lookup. First it's not clear to me what "range_lookup" is intended to mean and second it's an optional argument defaulting to the much-less-likely-to-be-what-you-want value of TRUE for approximate matching, rather than FALSE for exact matching.

So in my attempt to write VLOOKUP equivalents in Julia I've dropped the range_lookup argument and added another argument keycol_index_num to allow for searching of other than the first column of table_array.

WARNING I'm very new new to Julia, so there may be some howlers in the code below. But it seems to work for me. Julia 0.6.4. Also, and as already commented, using DataFrames might be a better solution for looking up values in an array-like structure.

#=
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Procedures: vlookup and vlookup_withfailsafe
Purpose : Inspired by Excel VLOOKUP. Searches a column of table_array for
  lookup_values and returns the corresponding elements from another column of
  table_array.

Arguments:
    lookup_values: a value or array of values to be searched for inside 
     column keycol_index_num of table_array.
    table_array: An array with two dimensions.
    failsafe: a single value. The return contains this value whenever an element
     of lookup_values is not found.
    col_index_num: the number of the column of table_array from which values
     are returned.
    keycol_index_num: the number of the column of table_array to be searched.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
=#
vlookup = function(lookup_values, table_array::AbstractArray, col_index_num::Int = 2, keycol_index_num::Int = 1)
    if ndims(table_array)!=2
         error("table_array must have 2 dimensions")
    end
    if isa(lookup_values,AbstractArray)
        indexes = indexin(lookup_values,table_array[:,keycol_index_num])
        if(any(indexes==0))
            error("at least one element of lookup_values not found in column $keycol_index_num of table_array")
        end
        return(table_array[indexes,col_index_num])
    else
        index = indexin([lookup_values],table_array[:,keycol_index_num])[1]
        if(index==0)
            error("lookup_values not found in column $keycol_index_num of table_array")
        end
        return(table_array[index,col_index_num])
    end
end

vlookup_withfailsafe = function(lookup_values, table_array::AbstractArray, failsafe, col_index_num::Int = 2, keycol_index_num::Int = 1)
    if ndims(table_array)!=2
        error("table_array must have 2 dimensions")
    end
    if !isa(failsafe,eltype(tablearray))
        error("failsafe must be of the same type as the elements of table_array")
    end
    if isa(lookup_values,AbstractArray)
        indexes = indexin(lookup_values,table_array[:,keycol_index_num])
        Result = Array{eltype(table_array)}(size(lookup_values))
        for i in 1:length(lookup_values)
            if(indexes[i]==0)
                Result[i] = failsafe
            else
                Result[i] = table_array[indexes[i],col_index_num]
            end
        end
        return(Result)
    else
        index = indexin([lookup_values],table_array[:,keycol_index_num])[1]
        if index == 0
            return(failsafe)
        else
            return(table_array[index,col_index_num])
        end
    end
end

Answer 2

the following code returns first matched from s details matrix using related record from a master matrix.

function vlook(master, detail, val)
  val = master[findfirst(x->x==val,master[:,2]),1]
  return detail[findfirst(x->x==val,detail[:,1]),2]
end

julia> vlook(a,b,103)
1005

A more general approach is to use DataFrame.jl, for working with tabular data.