VLookup Formula

[CarishmaM]

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?

[bruno29a]

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.

[elsamuel]

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.

[Experimenter]

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).

[Daniel_Weikert]

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

[CarishmaM]

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?

[Daniel_Weikert]

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

[ipazin]

Hello @CarishmaM,

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

Approximate match for numerical values with Threshold

I am trying to do an approximate match between two sheets. Paypal [image] Myob [image] In the Paypal sheet - I want to put the date if the transaction amount was found in myob sheet. [image] As sometimes the amount differs by +/-0.01 MYOB dates could not be found using a joiner or vlookup. In excel this problem can be solved using an approximate match but again you cannot define a threshold like 0.05 cents. Is there a way I can define a threshold and then try to match these table in KNI…

Rule Engine (Dictionary) INSTEAD OF VLOOKUP FUNCTION EXCEL?

Hello guys, I have a table creator like this: [image] And a Rule Engine (Dictionary) like this: [image] MY WORKFLOW LOOKS LIKE THIS: [image] The Logic behind: I like to lookup the “IPC-Hauptklasse” within my Table Creator and like to append a column with “Technologiefeld” within my FILE from the table reader according to the rule as you can see in the Rule Engine (dictionary) node. Here is a photo from table reader file: [image] Now i have a error code like this: [image] Do you k…

Br,
Ivan

[CarishmaM]

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".

[ipazin]

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

[morpheus]

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

[CarishmaM]

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.

[morpheus]

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

[CarishmaM]

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.

[Daniel_Weikert]

Have you tried a dictionary binner so far?
br

[CarishmaM]

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.