Multiple Conditions for Lookup (index/match) Columns

[SBW87]

Hi!

I am trying to return the size of a selection, when I specify the type and output required, which may not match the exact output, and therefore needs to find the closest match, for a certain type.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Type[/TD]
[TD]Output[/TD]
[TD]Size[/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]Single[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Single[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]Output[/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Double[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD][/TD]
[TD]Size[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Single[/TD]
[TD]300[/TD]
[TD]450[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Double[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any ideas would be greatly appreciated, many thanks.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try this array formula in F3
=INDEX(C$2:C$5,MATCH(MIN(IF(A$2:A$5=F1,ABS(B$2:B$5-F2))),IF(A$2:A$5=F1,ABS(B$2:B$5-F2)),0))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[SBW87]

Thank you so much!

I think that has worked perfectly, would you mind, whenever you have time, explaining the main functions in the formula?

 

[Marcelo Branco]

You are welcome. Glad to help.

The formula uses built-in functions: INDEX, MATCH, MIN, IF, ABS
Take a look at the help file.

M.