Mapping cells between between two ranges

Question

I have a couple ranges of data for which I need to map the values in one range to the values in the other range. I'm probably explaining this terribly, so here's an example below:

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 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 anyone think of ways to accomplish this using Excel formulas? I can't use VBA for this. As unlikely as it sounds, neither Range1 nor Range2 will have duplicate values, so I'm hoping that simplifies things a bit.

Edit: I'm limited to Excel 2016 functions as well. Sorry, I should have mentioned that before.

Answer 1

If you have Excel 365, you can convert the two ranges to columns using Tocol, then use xlookup to locate the matching cells:

=IF(BStrap1="","",XLOOKUP(BStrap1,TOCOL(Range1,1),TOCOL(Range2,1)))

I have changed BS1 to Bstrap1 because BS1 isn't a valid range name (looks like a cell reference).

---

In Excel 2016, you could do it with a formula which you copy down and across, so if the top left hand cell of your BS1 is in A9 say, you would have:

=IF(A9="","",SUM((Range1=A9)*Range2))

using the fact that there are no dups in Range1 and Range2 and also assuming that the ranges are defined with $ signs like this

Answer 2

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