(Solved) – Return the row with the most likely match

(solved)-–-return-the-row-with-the-most-likely-match

I am stuck on a problem regarding how to execute an vlookup for the most likely match

The basic theory is that I have a table with the business rules for example:

| Key1 | Key2 | Key3 | Key4 | ID |
|------|------|------|------|----|
| 1    | 2    | S    |      | 1  |
| 1    | 2    | R    | USD  | 2  |
|      | 1    | S    |      | 3  |
|      |      | S    |      | 4  |

And a transaction table as follows:

| Key1 | Key2 | Key3 | Key4 |
|------|------|------|------|
| 1    | 2    | S    | BRL  |
| 1    | 2    | R    | USD  |
| 2    | 2    | S    | USD  |
| 1    | 1    | S    | USS  |

The rules that I want to program are:

  1. If one of the Key matches add 1 to the likelihood of a match
  2. If one of the Keys don’t precisely match but the business rule is empty follow on, add 0
  3. Else subtract 1000 (Never use a rule with mismatched Keys)

How would you guys program this sudo vlookup function?

Thanks a lot for the help here is my table example result

For Row 1 of the transaction table the most likely rule ID is: 1

row 1 1 == 1 > 1, 2 == 2 > 1, S == S > 1, Key 4 is empty > 0, total rule likeness = 3
row 2 1 == 1 > 1, 2 == 2 > 1, R != S > -1000, USD == USD > 1, total rule likeness = -997
row 3 Key 1 is empty > 0, 1 != 2 > – 1000 …
row 4 S == S > 1, total rule likeness = 1

For the whole table of matches

| ID | Most Likely match |
|----|-------------------|
| 1  | 1                 |
| 2  | 2                 |
| 3  | 4                 |
| 4  | 3                 |

Thanks for the attention

Leave a Reply

Your email address will not be published. Required fields are marked *