Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching 1 to 1 and 1 with variations...
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching 1 to 1 and 1 with variations...
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$1 0=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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching 1 to 1 and 1 with variations...
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$1 0=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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching 1 to 1 and 1 with variations...
--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$1 0=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Many possible variations | Excel Worksheet Functions | |||
graphs with multiple variations | Excel Discussion (Misc queries) | |||
Variations on Sumif | Excel Worksheet Functions | |||
All variations of a set of numbers and letters | Excel Discussion (Misc queries) | |||
number variations | Excel Discussion (Misc queries) |