Vlookup on spilled data

[CV12]

Hello,

Lately I have been working on my array/spilling skills, and I have got yet another question about this:

In column A, I have got spilled data. In this small example my manual input ranges from 1 to 15.
In column G, the same numbers 1 to 15 are presented, with the corresponding values in column H (10 to 150).

Now in column B, I would like to have the values from column H.

Please note that the values in column A are spilled (is this the right word?) and the number of values can be variable.

Results.xlsx
	A	B	C	D	E	F	G	H
1	1	10					1	10
2	2	20					2	20
3	3	30					3	30
4	4	40					4	40
5	5	50					5	50
6	6	60					6	60
7	7	70					7	70
8	8	80					8	80
9	9	90					9	90
10	10	100					10	100
11	11	110					11	110
12	12	120					12	120
13	13	130					13	130
14	14	140					14	140
15	15	150					15	150
Sandbox

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	15	150					1	10
2	14	140					2	20
3	13	130					3	30
4	12	120					4	40
5	11	110					5	50
6	10	100					6	60
7	9	90					7	70
8	8	80					8	80
9	7	70					9	90
10	6	60					10	100
11	5	50					11	110
12	4	40					12	120
13	3	30					13	130
14	2	20					14	140
15	1	10					15	150
Main

Cell Formulas
Range	Formula
A1:A15	A1	=SEQUENCE(15,,15,-1)
B1:B15	B1	=XLOOKUP(A1#,G1:G15,H1:H15,"",0)
Dynamic array formulas.

 

[CV12]

Fluff,

Thanks for your reply. I dont think that I made my example good enough.
In my real example, columns G and H are one array (spill?).

I think that your formula automatically looks at both column G and H. is that true? I only need to use the data from column G

Results.xlsx
	G	H
1	Beam	N [kN]
2	1	-412.878
3	1	-412.878
4	2	-35.6254
5	2	-35.6254
6	3	-26.8165
7	3	-26.8165
8	4	-18.0077
9	4	-18.0077
10	5	-9.19884
11	5	-9.19884
12	6	-0.39
13	6	-0.39
14	7	8.41884
15	7	8.41884
16	8	17.22768
17	8	17.22768
18	9	26.03652
19	9	26.03652
20	10	34.84537
Helpersheet

Cell Formulas
Range	Formula
G2:H305	G2	=FILTER(FILTER(EXPORT3!C2:F1000000,(EXPORT3!C2:C1000000>=Sheet1!C46)*(EXPORT3!C2:C1000000<=Sheet1!C47)*(EXPORT3!B2:B1000000=Sheet1!C62)),{1,0,0,1})
Dynamic array formulas.

 

[Fluff]

I only need to use the data from column G

Do you mean you want to return the values from col H?

 

[Fluff]

Assuming you do still want to return the values from H, try

=VLOOKUP(A1#,G1#,2,0)

 

[CV12]

Made a new sheet to clearify.

I would like to search by using the values in column E.
The values in column E can be used to search in column H, the matching values are in column I.
Now, what I entered in G, should be the results,.

I think however, because columns E and F is one spill, it automatically uses both column E and F to search for values. I only need to search by using column E.

Not sure whether it is possible to split this up in the formula you just gave. If not, I can split up my original array formula.

Results.xlsx
	E	F	G	H	I
1	Beam	N [kN]
2	9131	-558.427	10	9131	10
3	9131	-558.427	10	9131	10
4	9132	-558.05865	20	9132	20
5	9132	-558.05865	20	9132	20
6	9133	-557.69031	30	9133	30
7	9133	-557.69031	30	9133	30
8	9134	-557.32196	40	9134	40
9	9134	-557.32196	40	9134	40
10	9135	-556.95361
11	9135	-556.95361
12	9136	-556.58527
13	9136	-556.58527
14	9137	-556.21692
15	9137	-556.21692
16	9138	-555.84857
17	9138	-555.84857
18	9139	-555.48022
19	9139	-555.48022
Helpersheet

Cell Formulas
Range	Formula
E2:F53	E2	=FILTER(FILTER(EXPORT3!C2:F1000000,(EXPORT3!C2:C1000000>=Sheet1!C46)*(EXPORT3!C2:C1000000<=Sheet1!C47)*(EXPORT3!B2:B1000000=Sheet1!C62)),{1,0,0,1})
Dynamic array formulas.

 

[Fluff]

In futur it is better to give full details, rather than wasting peoples time by giving inaccurate information that is nothing like your data.
Based on your original details try

=XLOOKUP(index(A1#,,1),G1:G15,H1:H15,"",0)

 

[CV12]

Thanks Fluff

 

[Fluff]

Glad to help & thanks for the feedback.