Lookup match index multiple values return other columns

Originally Posted by vlady

How about array formula index/match

=IFERROR(INDEX($D$1:$D$10,MATCH(E1,$A$1:$A$10&" "&$B$1:$B$10&" "&$C$1:$C$10,0)),"not found") -ctrl+shift+enter

note: please test on other instances of the names, didn't test it.

This is definatley on the right track but if the middle name in column C is missing (and this is likely - because I can't get my staff to consistently put them in) then there is no result returned