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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

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

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
>

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

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
>

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

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

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

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

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

Sorry meant to say the formula returns #N/A

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

Declan you Rock!

Thanks very much for your help!

Bob