# Function like VLOOKUP

**URL:** <https://discourse.julialang.org/t/function-like-vlookup/39814>\
**Category:** General Usage\
**Tags:** question, package, dataframes\
**Created:** [May 20, 2020, 9:51am UTC](https://discourse.julialang.org/t/function-like-vlookup/39814 "2020-05-20T09:51:28Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![yasser\_rajwani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yasser_rajwani/32/14278_2.png) [@yasser\_rajwani](https://discourse.julialang.org/u/yasser_rajwani)\
**Post date:** [May 20, 2020, 9:51am UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/1 "2020-05-20T09:51:29Z")

</div>

```julia

I have the following data and require a function like Vlookup in excel.

Table 1								
Criteria 1	Criteria 2 Factor 1
A 1 75
B 2 85
A 2 50
B 1 50

Table 2
Sample Criteria 1 Criteria 2
Sample 1 B 1
Sample 2 A 2

```

Require a function which can pick the right factor based on the criteria’s in table 2 from table 1, considering the criteria’s are in string format.

I tried using the filter function in the dataframes package but have not got the required results

---

<div class="post-metadata">

**Author:** ![dmolina](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dmolina/32/5246_2.png) [@dmolina](https://discourse.julialang.org/u/dmolina)\
**Post date:** [May 20, 2020, 10:04am UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/2 "2020-05-20T10:04:38Z")

</div>

Hi, @yasser_rajwani

You should see [Joins · DataFrames.jl](https://juliadata.github.io/DataFrames.jl/stable/man/joins/), last version includes many joins and I am sure one of them is useful in your case.

---

<div class="post-metadata">

**Author:** ![yasser\_rajwani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yasser_rajwani/32/14278_2.png) [@yasser\_rajwani](https://discourse.julialang.org/u/yasser_rajwani)\
**Post date:** [May 20, 2020, 10:46am UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/3 "2020-05-20T10:46:19Z")

</div>

Hi, @dmolina

Thank you for your reply.

I tried using the innerjoin function, however, am getting the following error

‘’’  
UndefVarError: innerjoin not defined

Stacktrace:  
[1] top-level scope at In[20]:2  
‘’’

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [May 20, 2020, 11:00am UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/4 "2020-05-20T11:00:30Z")

</div>

That probably means you’re not on the latest version of DataFrames, `innerjoin` was only introduced in the recent 0.21 update. Best thing is to update, if that isn’t possible you can use `join(..., kind = :inner)` on the previous versions.

---

<div class="post-metadata">

**Author:** ![yasser\_rajwani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yasser_rajwani/32/14278_2.png) [@yasser\_rajwani](https://discourse.julialang.org/u/yasser_rajwani)\
**Post date:** [May 20, 2020, 11:44am UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/5 "2020-05-20T11:44:19Z")

</div>

Thank you for your reply @nilshg

I am still unable to get the desired result using the join function, I require a function which enables me to look up / filter for a specific value that matches the criteria

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [May 20, 2020, 12:09pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/6 "2020-05-20T12:09:12Z")

</div>

Can you please send a specification of the input and the desired operation and I will propose you the ways how to achieve a desired result. Also please confirm what version of DataFrames.jl you are using (I recommend you to use version 0.21.0 as @nilshg suggested).

---

<div class="post-metadata">

**Author:** ![yasser\_rajwani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yasser_rajwani/32/14278_2.png) [@yasser\_rajwani](https://discourse.julialang.org/u/yasser_rajwani)\
**Post date:** [May 20, 2020, 12:21pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/7 "2020-05-20T12:21:17Z")

</div>

Hi @bkamins,

I have an input in the form of the following table  
‘’’

| Criteria 1 | Criteria 2 | Factor 1 |
| --- | --- | --- |
| A | 1 | 75 |
| B | 2 | 85 |
| A | 2 | 60 |
| B | 1 | 50 |
| ‘’’ | | |
| Sample | Criteria 1 | Criteria 2 |
| — | — | — |
| Sample 1 | B | 1 |
| Sample 2 | A | 2 |

‘’’  
I require a function that would return the value of 50 for sample 1 and value of 60 for sample 2 respectively from table 1.

I am also updating all my packages using

```julia
Pkg.update()

```

which should update the DataFrame package to the latest version.

Apologies I am a beginer on Julia and a bit lost

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [May 20, 2020, 12:50pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/8 "2020-05-20T12:50:41Z")

</div>

Under DataFrames.jl version 0.21.0 you can do:

```julia
julia> df1 = DataFrame("Criteria 1" => ["A","B","A","B"],
                       "Criteria 2" => [1, 2, 2, 1],
                       "Factor 1" => [75, 85, 60, 50])
4×3 DataFrame
│ Row │ Criteria 1 │ Criteria 2 │ Factor 1 │
│ │ String │ Int64 │ Int64 │
├─────┼────────────┼────────────┼──────────┤
│ 1 │ A │ 1 │ 75 │
│ 2 │ B │ 2 │ 85 │
│ 3 │ A │ 2 │ 60 │
│ 4 │ B │ 1 │ 50 │

julia> df2 = DataFrame("Criteria 1" => ["B","A"],
                       "Criteria 2" => [1, 2])
2×2 DataFrame
│ Row │ Criteria 1 │ Criteria 2 │
│ │ String │ Int64 │
├─────┼────────────┼────────────┤
│ 1 │ B │ 1 │
│ 2 │ A │ 2 │

julia> rightjoin(df1, df2, on=["Criteria 1", "Criteria 2"])
2×3 DataFrame
│ Row │ Criteria 1 │ Criteria 2 │ Factor 1 │
│ │ String? │ Int64? │ Int64? │
├─────┼────────────┼────────────┼──────────┤
│ 1 │ B │ 1 │ 50 │
│ 2 │ A │ 2 │ 60 │

```

a more advanced pattern is the following

```julia
julia> gdf = groupby(df1, 1:2)
GroupedDataFrame with 4 groups based on keys: Criteria 1, Criteria 2
First Group (1 row): Criteria 1 = "A", Criteria 2 = 1
│ Row │ Criteria 1 │ Criteria 2 │ Factor 1 │
│ │ String │ Int64 │ Int64 │
├─────┼────────────┼────────────┼──────────┤
│ 1 │ A │ 1 │ 75 │
⋮
Last Group (1 row): Criteria 1 = "B", Criteria 2 = 1
│ Row │ Criteria 1 │ Criteria 2 │ Factor 1 │
│ │ String │ Int64 │ Int64 │
├─────┼────────────┼────────────┼──────────┤
│ 1 │ B │ 1 │ 50 │

julia> gdf[NamedTuple(df2[1, :])]
1×3 SubDataFrame
│ Row │ Criteria 1 │ Criteria 2 │ Factor 1 │
│ │ String │ Int64 │ Int64 │
├─────┼────────────┼────────────┼──────────┤
│ 1 │ B │ 1 │ 50 │

julia> gdf[NamedTuple(df2[2, :])]
1×3 SubDataFrame
│ Row │ Criteria 1 │ Criteria 2 │ Factor 1 │
│ │ String │ Int64 │ Int64 │
├─────┼────────────┼────────────┼──────────┤
│ 1 │ A │ 2 │ 60 │

```

which allows you to do a lookup per row (if you wanted e.g. to do iteration).

* * *

Now related to package version. `Pkg.update()` does not have to give you the result you expect. I have recently written blog posts [here](https://bkamins.github.io/julialang/2020/05/11/package-version-restrictions.html) and [here](https://bkamins.github.io/julialang/2020/05/18/project-workflow.html) trying to explain the potential problems.

However, if you want to keep working in default project environment it is easiest to run `add DataFrames@v0.21` command in Package Manager mode that will make sure you have a right package version (in general I also recommend reading [this](https://julialang.github.io/Pkg.jl/v1/managing-packages/) part of the manual).

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [May 20, 2020, 1:40pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/9 "2020-05-20T13:40:50Z")

</div>

I think there is a bit of conceptual ambiguity here. In Julia and R, these kinds of lookup tables aren’t used very much compared to excel. Rather, we join everything into the same data frame.

If you are used to this kind of “lookup” workflow, which has advantages, you might want to consider using a `Dict` to organize information. However I also wrote a function which should work for your purposes.

```julia
function lookup(df, lookup_pairs, valcol)
       groupcols = first.(lookup_pairs)
       symbol_clean = [Symbol(first(p)) => last(p) for p in lookup_pairs] 
       nt = (;symbol_clean...)
       gdf = groupby(df, groupcols)
       out = gdf[nt][:, valcol]
       @assert length(out) == 1 # only want one observation
       return first(out)
 end

julia> lookup(df1, ["Criteria 1" => "A", "Criteria 2" => 1], "Factor 1")
75

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [May 20, 2020, 1:49pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/10 "2020-05-20T13:49:09Z")

</div>

I agree that vlookup is typically done in Julia using `Dict`s. However, what I wanted to highlight, is that `GroupedDataFrame` supports dictionary interface and provides a very fast lookup by grouping keys (with the same speed as if you used the `Dict` - actually internally we store `Dict` to do the lookup).

---

<div class="post-metadata">

**Author:** ![Juan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan/32/7657_2.png) [@Juan](https://discourse.julialang.org/u/Juan)\
**Post date:** [May 20, 2020, 3:47pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/11 "2020-05-20T15:47:00Z")

</div>

What is the advantage of the second method?

---

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [May 20, 2020, 3:59pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/12 "2020-05-20T15:59:02Z")

</div>

Here’s another way to do it:

```julia
using DataFrames

table1 = DataFrame(
    Criteria_1 = ["A", "B", "A", "B"],
    Criteria_2 = [1,2,2,1],
    Factor_1 = [75,85,60,50]
)

table2 = DataFrame(
    Sample = ["Sample 1", "Sample 2"],
    Criteria_1 = ["B", "A"],
    Criteria_2 = [1,2]
)

function get_factor(sample)
    crit1 = table2.Criteria_1[findfirst(x -> x == sample, table2.Sample)]
    crit2 = table2.Criteria_2[findfirst(x -> x == sample, table2.Sample)]
    return table1[(table1.Criteria_1 .== crit1) .& (table1.Criteria_2 .== crit2), :Factor_1][1]
end

julia> get_factor("Sample 1")
50

```

I’m not sure, but you might also just be trying to do what the last line of the `get_factor` function does, which would look like this:

```julia
julia> table1[(table1.Criteria_1 .== "B") .& (table1.Criteria_2 .== 1), :Factor_1][1]
50

# or if you want to return that whole row:

julia> table1[(table1.Criteria_1 .== "B") .& (table1.Criteria_2 .== 1), :]
1×3 DataFrame
│ Row │ Criteria_1 │ Criteria_2 │ Factor_1 │
│ │ String │ Int64 │ Int64 │
├─────┼────────────┼────────────┼──────────┤
│ 1 │ B │ 1 │ 50 │

```

I’ve not checked the performance of this so that may be a consideration if the tables you have are really large.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [May 20, 2020, 4:03pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/13 "2020-05-20T16:03:14Z")

</div>

The advantage is just that it looks more like the excel workflow you are used to. You actually input values into a function and get them back. If you are really comfortable using this workflow, then you can use one of the many methods outlined in this thread to do a lookup.

However a `join` is arguably more idiomatic in Julia for these kinds of operations. I would encourage you to explore `join`s more.

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [May 20, 2020, 4:14pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/14 "2020-05-20T16:14:32Z")

</div>

Arguably `VLOOKUP` isn’t even idiomatic in Excel though 🙂 - see e.g. [here](http://www.exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm)

---

<div class="post-metadata">

**Author:** ![yasser\_rajwani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yasser_rajwani/32/14278_2.png) [@yasser\_rajwani](https://discourse.julialang.org/u/yasser_rajwani)\
**Post date:** [May 21, 2020, 10:35am UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/15 "2020-05-21T10:35:34Z")

</div>

Thank you @bkamins,  
A sight iterarion of the suggested code worked well for me.  
Appreciate the help

---

<div class="post-metadata">

**Author:** ![yasser\_rajwani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yasser_rajwani/32/14278_2.png) [@yasser\_rajwani](https://discourse.julialang.org/u/yasser_rajwani)\
**Post date:** [May 21, 2020, 10:37am UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/16 "2020-05-21T10:37:08Z")

</div>

Thank you @pdeffebach and @mthelm85,  
appreicate the help

---

<div class="post-metadata">

**Author:** ![yasser\_rajwani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yasser_rajwani/32/14278_2.png) [@yasser\_rajwani](https://discourse.julialang.org/u/yasser_rajwani)\
**Post date:** [May 27, 2020, 2:14pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/17 "2020-05-27T14:14:37Z")

</div>

Hi @bkamins,

Thank you for your help on my previous question.

I have managed to complete my task using the code you suggested.

The next step that I want to learn is to run the same code for 100 samples instead of 2.

From my limited knowledge of julia, I understand that I need to use a loop, however, I have not been sucessful.

Would you be kind enough to help out ?

Thanks You.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [May 27, 2020, 2:17pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/18 "2020-05-27T14:17:39Z")

</div>

Can you show us what you have tried? Try posting the error.

If you error is something along the lines of

```julia
ERROR: UndefVarError: s not defined

```

Then make sure you put your code in a `let` block.

For example, if you are running the code

```julia
julia> s = 0
julia> for i in 1:10
       s = s + i
       end

```

The solution is to do

```julia
julia> s = let t = 0
       for i in 1:10
           t = t + i
       end
       t
       end

```

---

<div class="post-metadata">

**Author:** ![yasser\_rajwani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yasser_rajwani/32/14278_2.png) [@yasser\_rajwani](https://discourse.julialang.org/u/yasser_rajwani)\
**Post date:** [May 27, 2020, 3:01pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/19 "2020-05-27T15:01:41Z")

</div>

Hi @pdeffebach,

The code that I have written is

‘’’  
Y1 = (Data[1:15,10].\* ((1 .- Data[1:15,4]) + (0.3 .\* Data[1:15,4])))

‘’’

I now need the Y1 to go Y2,

‘’’  
Y2 = (Data[1:15,11].\* ((1 .- Data[1:15,5]) + (0.3 .\* Data[1:15,5])))

‘’’  
As you can observe, some columns need to move increase by 1 (which I am hoping to code using the loop function)

I would like this to go on till Y10, with the columns changing (increase by 1)

Apologies, if this is difficult to understand.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [May 27, 2020, 3:23pm UTC](https://discourse.julialang.org/t/function-like-vlookup/39814/20 "2020-05-27T15:23:14Z")

</div>

Thanks for the detailed response. One quick thing, the correct way to quote code is with back-ticks. You are using quotation marks.

````julia
``` 
like this
```

````

The correct way to do this would be with a loop. Let’s store the output of our computation in a new data frame

```julia
julia> Data = DataFrame(rand(15, 20)); # simulated data frame

julia> out = DataFrame(); # an empty data frame to add columns to

julia> for i in 1:10
           out[:, "Y$i"] = # "Y$i" makes a new column 
                           # with names "Y1" etc.
               Data[1:15, i + 9] .* ((1 .- Data[1:15, i + 4]) .+ (.3 .* Data[1:15, i + 4]))
       end

julia> out

```