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.

	A	B	C	D	E	F
1	Type	Output	Size		Type	Single
2	Single	200	300		Output	175
3	Double	100	150		Size	XXXX
4	Single	300	450
5	Double	200	300

<tbody>
</tbody>

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.