How to complete XLOOKUP across a row but have the lookup array change dynamically by column in another table

[armsafna]

For example: let's say I have a six-sided die with sides A through F.

I roll the die four times and record results per trial as follows:

Trial	Roll 1	Roll 2	Roll 3	Roll 4
1	A	B	C	D
2	E	F	A	B
3	E	D	C	F

Now I have another table where I want to output the roll during which each letter was the outcome per trial:

Outcome	Trial 1	Trial 2	Trial 3
A	=XLOOKUP($A6,$B2:$E2,$B$1:$E$1) = Roll 1	=XLOOKUP($A6,$B2:$E2,$B$1:$E$1) = Roll 1 (I want) =XLOOKUP($A6,$B3:$E3,$B$1:$E$1) = Roll 3
B	=XLOOKUP($A7,$B2:$E2,$B$1:$E$1) = Roll 2
C	=XLOOKUP($A8,$B2:$E2,$B$1:$E$1) = Roll 3
D	=XLOOKUP($A9,$B2:$E2,$B$1:$E$1) = Roll 4
E	=XLOOKUP($A10,$B2:$E2,$B$1:$E$1) = N/A
F	=XLOOKUP($A11,$B2:$E2,$B$1:$E$1) = N/A

It works great for the column 'Trial 1', updating the lookup value. When I try to drag it across the column, I want it to change the lookup table from B2:E2 to B3:E3 corresponding to the Trial 2 results which are displayed along a row rather than a column in the other table. See the 'Trial 2' column for an example of the error in bold. What I want it to do is recognize I am referring to Trial 2, and search for A6 (the letter "A") in the 'Trial 2' row of table 1, and therefore dynamically adjust the lookup table to B3:E3. Is there a way to do this without transposing Table 2 to match Table 1? (i.e. with trials in columns instead of rows?)

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E
1	Trial	Roll 1	Roll 2	Roll 3	Roll 4
2	1	A	B	C	D
3	2	E	F	A	B
4	3	E	D	C	F
5
6	Outcome	Trial 1	Trial 2	Trial 3
7	A	Roll 1	Roll 3	#N/A
8	B	Roll 2	Roll 4	#N/A
9	C	Roll 3	#N/A	Roll 3
10	D	Roll 4	#N/A	Roll 2
11	E	#N/A	Roll 1	Roll 1
12	F	#N/A	Roll 2	Roll 4
Sheet4

Cell Formulas
Range	Formula
B7:D12	B7	=INDEX($B1:$E1,MATCH($A7:$A12,FILTER($B2:$E4,$A2:$A4=RIGHT(B6)+0),0))
Dynamic array formulas.

Do you have to worry about getting the same result more than once in a given trial? 2 A's for example?

 

[armsafna]

Worked like a charm thank you! I imagine that as a general rule using INDEX and MATCH where the row is filtered to match a value in a column in another table works to solve this as a general issue?

 

[Eric W]

Well, every situation is different. I could have used OFFSET instead of FILTER, and it would have worked OK. But OFFSET is volatile (it recalculates every time anything on the sheet recalculates, potentially slowing the sheet down), so most people avoid it whenever possible. Using FILTER is definitely a tool to keep in your bag of tricks though, especially when the rows are easily identified as they are in this example.

Glad I could help!