Multi Criteria Index/Match to find nearest value

[troglodyte]

Hi all,

I need help with (what I'm sure is!) a basic Index/Match problem which I can't wrap my head around.

I need to find the closest 'Fixed Value' (Column C) which relates to the list of 'Actual Values' (Column D) in column E. The other criteria here is that the value must come from the same Grouping, i.e. The value in cell D2 is in Group A, the closest value returned must also be in Group A (as determined by the letters in column B).

So far in E2 I have returned the closest value overall (for all groups) but not the one which is closest to a 'Fixed Value' from Group A. How can I amend my formula (seen below) in E2 so that I can return only the nearest number from the relevant group? A second point I'd possibly like to add is that I would want the name (in Column A) to be returned instead of the number.

=INDEX($C$2:$C$100000,MATCH(MIN(ABS($C$2:$C$100000-D2)),ABS($C$2:$C$100000-D2),0))

Below is an example of what I have (I can't attach the file sadly).

Name	Group	Fixed Value	Actual Value	Closest Value
Harry	A	12	156531	15665
David	B	13	466
Charlie	C	15665	132
Henry	D	651651	5
Simon	A	648	45618
Alex	D	351	156
Larry	D	984	168
Marvin	D	5	51
Tim	D	984561	681
Jeremy	A	894	3251
Tom	B	2516	259
Pat	B	5684	651
Adam	C	561	56
Dave	C	656	353

<tbody>
</tbody>

Many thanks,

 

[gaz_chops]

Try
=INDEX($C$2:$C$100000,MATCH(MIN(IF($B$2:$B$100000=B2,ABS($C$2:$C$100000-D2))),ABS($C$2:$C$100000-D2),0))

 

[XOR LX]

Try
=INDEX($C$2:$C$100000,MATCH(MIN(IF($B$2:$B$100000=B2,ABS($C$2:$C$100000-D2))),ABS($C$2:$C$100000-D2),0))

You need to repeat the conditional clause(s) within MATCH's lookup_array, otherwise you're not guaranteed correct results. What's more, I think we also need to exclude the case for the person pertaining to the row in question, i.e.:

=INDEX($A$2:$A$15,MATCH(MIN(IF($B$2:$B$15=B2,IF($A$2:$A$15<>A2,ABS($C$2:$C$15-D2)))),IF($B$2:$B$15=B2,IF($A$2:$A$15<>A2,ABS($C$2:$C$15-D2))),0))

@troglodyte I've used an upper range reference of only 15. Do you really need to go as far as row 100,000? These formulas are likely to bring your computer to a near standstill if that is the case.

Regards

 

[troglodyte]

Try
=INDEX($C$2:$C$100000,MATCH(MIN(IF($B$2:$B$100000=B2,ABS($C$2:$C$100000-D2))),ABS($C$2:$C$100000-D2),0))

Thanks for your help!

You need to repeat the conditional clause(s) within MATCH's lookup_array, otherwise you're not guaranteed correct results. What's more, I think we also need to exclude the case for the person pertaining to the row in question, i.e.:

=INDEX($A$2:$A$15,MATCH(MIN(IF($B$2:$B$15=B2,IF($A$2:$A$15<>A2,ABS($C$2:$C$15-D2)))),IF($B$2:$B$15=B2,IF($A$2:$A$15<>A2,ABS($C$2:$C$15-D2))),0))

@troglodyte I've used an upper range reference of only 15. Do you really need to go as far as row 100,000? These formulas are likely to bring your computer to a near standstill if that is the case.

Regards

Thanks for your input! The 100,000 was merely an effort to 'future-proof' this sheet for when it eventually grows to contain that much data (it most likely won't be 100,000 rows!). At present the sheet has just under 3,000 rows.

Once again thanks for your help here guys

 

[XOR LX]

But array formulas calculate over all cells within the ranges passed to them, whether beyond the last-used cells in those ranges or not. So it's not like you can choose an arbitrarily large upper range reference with no detriment to performance: thought has to go into this choice.

And I take it you also noted my point about the error in gaz_chops' formula?

Regards

 

[troglodyte]

But array formulas calculate over all cells within the ranges passed to them, whether beyond the last-used cells in those ranges or not. So it's not like you can choose an arbitrarily large upper range reference with no detriment to performance: thought has to go into this choice.

And I take it you also noted my point about the error in gaz_chops' formula?

Regards

I did notice -thank you, it works perfectly.

 

[gaz_chops]

You're welcome, as XOR LX pointed out, you should add it to the Match's lookup array, i missed it!

=INDEX($C$2:$C$15,MATCH(MIN(IF($B$2:$B$15=B2,ABS($C$2:$C$15-D2))),IF($B$2:$B$15=B2,ABS($C$2:$C$15-D2)),0))

 

[XOR LX]

But I also think we need to exclude the row that the formula's in, no? Which is why I had the additional clause in my formula.

The reason is that we don't want the answer for e.g. "Harry" to be "Harry", correct? It has to be a different person.

Regards

 

[troglodyte]

But I also think we need to exclude the row that the formula's in, no? Which is why I had the additional clause in my formula.

The reason is that we don't want the answer for e.g. "Harry" to be "Harry", correct? It has to be a different person.

Regards

You are correct, I was looking for a different person. Apologies for not making that clearer.