Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
I have a worksheet that has 6 columns of numerical data that all column
datasets are referenced together for each row. So for example the data in row 5 in all colums is related. Column C through F have 4 numbers that refer to the data in the first 2 columns. These are also ordered by row. I need to look in column A for 0.00, and column B for 3.14 Both of these must be in the same row or I need the closest value. When these values are found in columns A & B, return the values from the same row in Columns C through F. Thanks in advance, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Hi!
I need to look in column A for 0.00, and column B for 3.14 Both of these must be in the same row or I need the closest value. Closest value to which? Closest to zero in column A or closest to 3.14 in column B? Can you post an example and a more detailed explanation? Biff "Bob" wrote in message oups.com... I have a worksheet that has 6 columns of numerical data that all column datasets are referenced together for each row. So for example the data in row 5 in all colums is related. Column C through F have 4 numbers that refer to the data in the first 2 columns. These are also ordered by row. I need to look in column A for 0.00, and column B for 3.14 Both of these must be in the same row or I need the closest value. When these values are found in columns A & B, return the values from the same row in Columns C through F. Thanks in advance, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
I need the closest values to both 0.00 on column A and 3.14 in column b
So for example: A B 1.2 9.5 0.7 4.3 ..003 3.2 -.02 1.5 -.08 -3.9 So for this example Row 3 is the closest to the target values. The sheet would return The values from row 3 in columns C,D,E and F I hope that's much clearer! Thanks! Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Hi Bob
Would you be prepared to insert a new column at C (hidden if required)? If so, in this column enter the formula =ABS((A1+B1)-3.14) Then =INDEX(D1:D5,MATCH(MIN($C$1:$C$5),$C$1:$C$5)) Drag across to pick up the data from columns E,F and G. Regards Roger Govier Bob wrote: I need the closest values to both 0.00 on column A and 3.14 in column b So for example: A B 1.2 9.5 0.7 4.3 .003 3.2 -.02 1.5 -.08 -3.9 So for this example Row 3 is the closest to the target values. The sheet would return The values from row 3 in columns C,D,E and F I hope that's much clearer! Thanks! Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Thanks Roger,
But this method gets fooled by column A values that are close to 3.14 and Column B values that are close to 0. Bob |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Hi Bob
Very true!!! Are there any limits, that the A values would fall within, or the B values would fall within? Regards Roger Govier Bob wrote: Thanks Roger, But this method gets fooled by column A values that are close to 3.14 and Column B values that are close to 0. Bob |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Sure Roger, both will be any value from 30 to -20
Kind of a wide spread. The tough part is getting the location of these two values in line and returning the C through F values. Thanks for the help! Bob |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Assuming data is in sheet1, on another sheet, in A1, put your target
value for column A (0 in the current example), and in B1 put your target value for column B (3.14 in the example). In C1 of that sheet put =INDEX(Sheet1!F:F,MATCH(MIN(ABS(Sheet1!$A$1:$A$5-$A$1)+ABS(Sheet1!$B$1:$B$5-$B$1)),ABS(Sheet1!$A$1:$A$5-$A$1)+ABS(Sheet1!$B$1:$B$5-$B$1),0)) Enter as an array formula (ctl-shift-enter) and drag across across to F. The values from the appropriate row in Sheet1 should appear. This will find the minimum total absolute differences between the target for A and column A, and the target for B and column B. If you need to define "closest" any other way, a different approach may be needed. For example, you could be looking for the smallest total absolute percentage difference, or you could use least squares differences, which would produce different results, and need a different approach. If you are prepared to use a helper column as others have suggested, the formula can be greatly simplified and could easily be modified to use different definitions of "closest", and it need not be an array formula. Hope this helps. Declan O'R |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Thanks Declan,
I have values in columns C,D,E and F that this formula must return when it finds the appropriate values in columns A&B Let me clairify: After we find the row with 0.0 in A and 3.14 in B the values from this same row (Let's say row 5 ) from C,D, E and F are returned from row 5. I hope that helps! Thanks again! Bob |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Did the formula I provided not do what you need if you put 0 in A1 and
3.14 in B1 in the second sheet? I suggested a second sheet because I did not know what else you had on your sheet with the data. In my test sheet it did exactly what you say you need, returning the values from row 3 in your test case - C3, D3, E3 and F3, and putting those values in C1 through F1 in the second sheet. We could, of course, have put the values of 0 and 3.14 in the formulas instead of A1 and B1, but I have a religious objection to putting values like that in formulas <g, given the maintenance headache they cause when you need to change them. Am I still missing something as to what you need? Declan |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Hi Declan,
Maybe I misunderstood your explaination, but what results is just ####'s. The formula appears to be looking in column F:F for something, and not returning anything. I'm a bit concerned with the use of ABS since the values I may need must be positive only or match closely to the target values in A1 and B1 I can separate the data on a single sheet, maybe the double sheet thing has me out to lunch. Again maybe I don't understand and I'm trying to use this wrong, so any help you could give would be appreciated. Thanks, Bob |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Sorry meant to say the formula returns #N/A
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
OOOPS! Bob,
I copied the fromula from F1 and told you to put it in C1. I should have copied the formula from column C ....Here is the one you should have put in C1: =INDEX(Sheet1!C:C,MATCH(MIN(ABS(Sheet1!$A$1:$A$5-$A$1)+ABS(Sheet1!$B$1:$B$5-$B$1)),ABS(Sheet1!$A$1:$A$5-$A$1)+ABS(Sheet1!$B$1:$B$5-$B$1),0)) Despite this, you should still have got a value in the first column (frm column F) and zeros or wahtever is to the the right of column F in sheet1. You may need to be careful when copying it from here to your sheet as a hyphen may be inserted at the line break in this display. Also, be sure you enter it as an array formula before you drag it across. It does work as is on my test sheet. If you want to do it on one sheet, assume you put your target values in G1 and H1, then put this formula in I1 and drag across for a total of 4 columns: =INDEX(C:C,MATCH(MIN(ABS($A$1:$A$5-$G$1)+ABS(Sheet1!$B$1:$B$5-$H$1)),ABS($A$1:$A$5-$G$1)+ABS($B$1:$B$5-$H$1),0)) This should work. Let me know if you want the simpler version using a helper column and tell me where you want the target values and helper column to be. BTW, the absolute functions are necessary because you are looking for the minimum total difference in the two columns, one could be postitive and one negative and might offset each other, e.g. -2 and +2, which would be less than the total of two differences that were each only ..001. Hope this works for you. Declan O'R |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match function for multiple linked variables
Declan you Rock!
Thanks very much for your help! Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Function Vlookup, Match or Index? | Excel Worksheet Functions | |||
Variable values in Index function | Excel Worksheet Functions |