# VLookup Formula

This is a Vlookup Formula that i am trying to duplicate in Knime:

=VLOOKUP(LEFT(F34,6)*1,‘Recurring Support’!A:B,2,TRUE)

Do you have any suggestions of how to do so?

Hi @CarishmaM , I’m not really an Excel user. I use it only for it’s tabular capacity and applying minimal formulas, so I’m not too familiar with how vlookup works. My understanding of it is that it looks up for something from another table/sheet, and this usually translate to a JOIN.

If you can explain a bit what your vlookup statement is doing, then may be I can translate to Knime.

Some sample data could also help along with what the expected results should be.

1 Like

As @bruno29a mentioned, this is most likely going to be accomplished with a Joiner node.

You will need to do some pre-processing (with a Column Expressions node or Rule Engine node or String Manipulation node, etc) to get the matching criteria right (e.g. replicating the LEFT formula from Excel).

It would be best if you gave us some data work work with and a clearly idea of what you’re trying to do.

1 Like

Excel’s VLOOKUP is a miserable attempt to mimic something like join functionality. In Knime you

1. create column with your lookup value (looks like String manipulation would do) in your general data sheet (a)
2. use Joiner node to join value from your “Recurring Support” (b) to your general data sheet(a), using as join fields your newly created one in (a) and the 1st column from the (b).
1 Like

There are 2 “VLOOKUP” functions in KNIME.
The first one is cell replacer node. The other one is joiner node.
hope that helps
br

1 Like

So, I realized that the VLook Up Formula is rounding the Look Up Value to either equal or less than what is listed in the recurring support.

So if the look up value is 558339

But the recurring support does not have it but has 558338, it will match that. Is there a way to get that done in Knime?

1 Like

So you are talking about the approximate match in excel instead of the exact match?
You could have a look at the math formula and there at the round, ceil, floor… functions

1 Like

Hello @CarishmaM,

have found two topics regarding approximate match. Maybe they help:

Br,
Ivan

4 Likes

Hi Ivan,

I did see those, but for some reason I am unable to get the workflow in the first suggestion to work regarding the " Best guess node".

Hello @CarishmaM,

hard to tell what’s wrong. Can you share some workflow with data (dummy works just fine) and desired output so we can give it a try.

Br,
Ivan

2 Likes

Hi @CarishmaM,
due to the settings of your vlookup for approximate match a simple joiner node does not work. But you can loop through your table and do a cross join with your reference table, calculate the delta and return the record with the least delta value.

BR

1 Like

Hi,

I want to a do a VLookUp Approximate Match in which:

If the value is 589663, but in the Look up table it does not have 589663. I would like the system to match the next value below 589663. So, if it has 589660, it would match 589663 with 589660 and pull the lookup value associated with 589660.

Hi @CarishmaM,
you didn’t profide an example, therefore i used a simple case i had worked on. This case is based on Timestamps (LocalDateTime). The goal is to assign the last Medication to a List of Samplings. But in general it would work with small adaptions also in your case.

Testfile:
Referencefile:

Result:

Workflow:

Example.knwf (505.6 KB)

BR

1 Like

Hi,

I thought provide an example earlier, and I am not really sure how to provide more detail. This is my current workflow:

I’m not sure what the difference would be because the prior table automatically looks up the lesser value. The Look Up value doesn’t have a consistent trend where it would be easy to say - reduce value by 5.

The original data looks this:

And it is reduced to the first 6 digits from the left. So, 857939, 908246, 828476 and then matched to data like this:

If the exact match is found the lookup value in Column 2 will replicate. If not, it will automatically pull the next value that is below the one being searched. So for in the instance, 857939 does not exist but the lesser value 857931 does,. Consequently, it would pull the look up value for 857931.

Have you tried a dictionary binner so far?
br

I was able to test Binner, and I found that it matched the 1 column to the lowest value of the 2nd column even though there was an equal value, or a value that was a lower than the current. For example:

This occurred even though the inclusive box was selected.

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.