matching 1 to 1 and 1 with variations...

[srmyers1]

I have a workbook with 2 worksheets, the formula will need to be placed in
worksheet 2. I need to match data from worksheet 1 with data in worksheet 2
and return a value listed on the row in worksheet 1. Here's how it is set
up...

Sheet 1
Column A - Location Code
Column B - Segment
Column C - Post

Sheet 2
Column A - Segment
Column B - post

The "post" columns in the 2 worksheets do not necessarily match. In
worksheet 2 I need to match the segment in worksheet 1, take the "post" in
worksheet 2 and match it 1 to 1, or if the post is +/- .25 from the post
listed in worksheet 1, if it is in that range then I need to return the
location code...

"segment" is set up as a whole number, no decimals, and "post" is a whole
number with 3 decimal places.

 

[Jacob Skaria]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

Try this formula from Sheet2 with segment and post in cell a1 and b1

=INDEX(Sheet1!$A$1:$A$10,MATCH(1,(Sheet1!$B$1:$B$10=A1)*
((ROUND(Sheet1!$C$1:$C$10/0.25,0)*0.25)=ROUND(B1/0.25,0)*0.25),0))

If this post helps click Yes

 

[srmyers1]

Thanks Jacob, it seems to be working well.... What if I need to change the
tolerance from +/- .25 to +/- .50, +/- .75, or +/- 1.0, would it work the
same if I changed the 0.25 values in the formula to the new tolerance?

 

[Jacob Skaria]

--It should work provided you have matching value in that range..If you want
to handle mismatch errors try =IF(ISNA(formula),"",formula)

If this post helps click Yes