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
>

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