CODEWITHSUNDEEP
excelexcel-formulaspreadsheetvba
Carol.Kar
Carol.Kar

Reputation: 5205

Match 2 columns based on a % difference within the value

I am looking for a method to match two excel tables.

I basically have two Systems, where the values do not exactly match only some IDs. The values in system 2 are usually 10-20% different from system 1.

Here is how the sheet looks like: enter image description here

I tried to use vlookup on the IDs and then going hand-by-hand through the values if they match, by using the filter with the ID. However, this takes extremely long and is very cumbersome.

Any recommendation how to match these two tables, much more easily?

I really appreciate your replies!

Upvotes: 0

Views: 1085

Answers (1)

user4039065
user4039065

Reputation:

If you look at a formula for G3 you would be involving D3:E3 and A:B (where A10:B10 are the matching values).

When someone states that they are looking for a percentage, it is helpful to know "a percentage of what...?". You receive a different result if the calculation is ABS(12 - 15)/15 instead of ABS(12 - 15)/12. One may within tolerance and the other may not.

In any event, the formula for G3 would be something like,

=ABS(E3-VLOOKUP(D3,A:B, 2, FALSE))/E3 ... or,

=ABS(E3-VLOOKUP(D3,A:B, 2, FALSE))/VLOOKUP(D3,A:B, 2, FALSE)

That produces a result of 0.25% or 0.20% depending on how you calculate the percentage. You could wrap that in an IF statement for a YES/NO text result or use a custom number format like [Color3][>0.2]\NO;;[Color10]\Y\E\S;@ which will show a red NO for values greater than 20% and a green YES for values between 0 and 20%. Negative values do not have to be accounted for as the ABS removes them from consideration.

       Percent YES

I've only reproduced a minimum of your sample data for demonstration purposes but perhaps you can get an idea on how to proceed from that.

Upvotes: 1

Related Questions