Match prices within a range

**TobiasFK** · 03-04-2011, 05:29 AM

Hi, I need a function which enables me to check if two sets of data is identical. Look at my attached example.

funcA should match the price in column C with the price in column H if the number from column A are identical with the number from column F.
It should evaluate the price within a range of +/-1.00 (fx: the function will positively match the prices in row 2, because the difference between 224,067.87 and 224,067.47 are less than +/-1.00).
If the function are able to find a positive match, it should return "OK"
If the function are not able to find a positive match, it should return "DIFF"

Likewise, funcB should match the price in column H with the price in column C if the number from column F are identical with the number from column A.
It should consider the same criteria as funcA.

I sincerely appreciate your time. I know this is a challenging task!

In advance, thank you!

BR
Tobias

**dbspec** · 03-04-2011, 06:08 AM

I don't think that is not possible using formula especially in excel 2003 and earlier version. There is no such lookup function with range parameter input.I.e. the input is range number.
IMHO, Writing VBA code is your only option.

**TobiasFK** · 03-04-2011, 06:13 AM

Thank you, I can imagine.

is there anyone who can help me writing this macro?

Thanks
/tobias

**canapone** · 03-04-2011, 06:56 AM

Hi,
the followings do not exactly what you're asking, they can be misleading in case of more than one number falls in the range (in your example very small numbers can be tricky).

Anyway (disclaimer is over) you could try in D2:

=IF(SUMPRODUCT(($H$2:$H$20<=C2+1)*($H$2:$H$20>=C2-1))>0,"OK","DIFF")

in E2 an array formula to be confirmed with control+shift+enter

=IF(ISNA(VLOOKUP(C2,$H$2:$H$20,1,0)),MAX(IF((($H$2:$H$20<=C2+1)*($H$2:$H$20>=C2-1)),$H$2:$H$20)),VLOOKUP(C2,$H$2:$H$20,1,0))

Just for sharing some idea.

Regards

**brokenbiscuits** · 03-04-2011, 07:26 AM

Just to check, you have more than one instance of some numbers in Col B - for instance 71-1-1 appears in rows 2 and 9. Do we need to match column A and B against cols F and G?

**TobiasFK** · 03-04-2011, 07:40 AM

Originally Posted by brokenbiscuits

Just to check, you have more than one instance of some numbers in Col B - for instance 71-1-1 appears in rows 2 and 9. Do we need to match column A and B against cols F and G?

Sorry, yes, that will be extremely valuable. The function/VBA should do the following:

1) Find value in col. A in col. F, 2) then find value in col. B in col. G, 3) then check if the price in col. C matches the price in col. H (within the range of +/-1.00).

So, for instance, the price in C2 will be matched with the price in H2, because A2=F2 and B2=G2. It should not match with the prices which do not fulfill this (otherwise, there could more instances of the same price)

Sincerely, thank you!