matching 1 to 1 and 1 with variations...

srmyers1 · External Since: Nov 06, 2009 Posts: 2

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 · External Since: Mar 04, 2009 Posts: 441

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
---------------
Jacob Skaria

"srmyers1" wrote:

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

srmyers1 · External Since: Nov 06, 2009 Posts: 2

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" wrote:

> 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
> ---------------
> Jacob Skaria
>
>
> "srmyers1" wrote:
>
> > 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 · External Since: Mar 04, 2009 Posts: 441

--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
---------------
Jacob Skaria

"srmyers1" wrote:

> 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" wrote:
>
> > 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
> > ---------------
> > Jacob Skaria
> >
> >
> > "srmyers1" wrote:
> >
> > > 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.