IF MATCH formula to exclude blank cells in range

Hi,

I was wondering if anyone could help me with a formula I've been having immense trouble figuring out how to write. In my Data Set to Use for Comparison column, I would like to pull in the closest matching value to my actual result, but exclude any blank values. So for example, in Row 10, where the only Data value is 14, I would like my formula to pull back the number 14 rather than the blank values.

I've worked out the below formula if there were only two data sets to use, but cannot figure out how to create a formula to include 4 data sets:
={IF(AND(B2="",C2<>""),C2,IF(AND(B2<>"",C2=""),B2,IF(AND(B2="",C2=""),"",IF(AND(B2<>"",C2<>""),INDEX(B2:C2,MATCH(MIN(ABS(B2:C2-A2)),ABS(B2:C2-A2),0))))))}

Is there a relatively simple way to either add to or rewrite the above formula to include cells from columns C and D in the range, but to exclude any blank cells from the entire range?

Thanks

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.