Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Index Match function for multiple linked variables

Sorry meant to say the formula returns #N/A

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Index Match function for multiple linked variables

Declan you Rock!

Thanks very much for your help!

Bob

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Match function with duplicate values in an array Richard Excel Worksheet Functions 3 April 22nd 23 07:45 PM
Match Index cjjoo Excel Worksheet Functions 3 October 25th 05 09:33 AM
Match or Index Question carl Excel Worksheet Functions 2 October 4th 05 09:11 PM
Function Vlookup, Match or Index? Patrick Young Excel Worksheet Functions 0 October 3rd 05 11:08 PM
Variable values in Index function mlkpied Excel Worksheet Functions 6 December 7th 04 12:38 AM


All times are GMT +1. The time now is 12:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"