Mapping cells between between two ranges

Question

I need to translate the values in one data range to the values in the other range because I have two data ranges. I'm probably doing a terrible job of describing this, but here's an illustration:

I have a range with standardized residuals (Range1):

1.7	0.6	-3.6
-1.5	-0.8	3.6
-0.2	0.2

And a second set of standardized residuals (Range2)

-2.6	1.7	0.7
1.5	-0.7	-0.7
1.2	-1.2

I take a bootstrapped sample from Range1 and get this output (BS1).

0.6	-3.6	-1.5	3.6
-0.2	0.6	-0.2
-0.2	-3.6
1.7

Now I want to use BS1 to get a bootstrapped sample of Range2 (BS2) by using the location of each BS1 value in Range1.

For example. BS1(1,1) = 0.6. That corresponds to Range1(1,2). So, BS2(1,1) = Range2(1,2) = 1.7. I want to end up with this table, BS2.

1.7	0.7	1.5	-0.7
1.2	1.7	1.2
1.2	0.7
-2.6

Can someone come up with a way to do this using formulas in Excel? VBA won't work for this. I'm hoping that makes things a little bit easier since, despite how unlikely it may sound, neither Range1 nor Range2 will have duplicate values.

Answer 1

Try this:

=IF(B4:H10<>"",B14:H20,"")