Double V lookup

[jrisebo]

How do you do a double Vlookup for multiple lookups.
I want to lookup column R, and then the row that has a "yes" from column S, then the values for the yes column in 6 different cells.

So, if R=7.5, and then the row with "yes" i want the 6 values from that row in 6 different cells

Open Roof Structure Wind Load - Part 5 ASCE 7.xlsx
	R	S	T	U	V	W	X	Y
44	0	no	2.4	-3.3	1.8	-1.7	1.2	-1.1
45		no	1.8	-1.7	1.8	-1.7	1.2	-1.1
46		yes	1.2	-1.1	1.2	-1.1	1.2	-1.1
47	7.5	no	3.2	-4.2	2.4	-2.1	1.6	-1.4
48		no	2.4	-2.1	2.4	-2.1	1.6	-1.4
49		yes	1.6	-1.4	1.6	-1.4	1.6	-1.4
Beam design

Cell Formulas
Range	Formula
S44,S47	S44	=$O$9
S45,S48	S45	=$O$10
S46,S49	S46	=$O$11

 

[SanjayGMusafir]

So, if R=7.5, and then the row with "yes" i want the 6 values from that row in 6 different cells

That could be not be difficult but you can't keep cells merged like in Column R of your sample data

Check this -

All Records.xlsb
	R	S	T	U	V	W	X	Y
44	0	no	2.4	-3.3	1.8	-1.7	1.2	-1.1
45	0	no	1.8	-1.7	1.8	-1.7	1.2	-1.1
46	0	yes	1.2	-1.1	1.2	-1.1	1.2	-1.1
47	7.5	no	3.2	-4.2	2.4	-2.1	1.6	-1.4
48	7.5	no	2.4	-2.1	2.4	-2.1	1.6	-1.4
49	7.5	yes	1.6	-1.4	1.6	-1.4	1.6	-1.4
50
51
52	LOOKUP
53	0 & Yes		1.2	-1.1	1.2	-1.1	1.2	-1.1
54	7.5 & Yes		1.6	-1.4	1.6	-1.4	1.6	-1.4
Sheet1

Cell Formulas
Range	Formula
T53:Y53	T53	=XLOOKUP(1,($R$44:$R$49=0)*($S$44:$S$49="yes"),T$44:T$49,)
T54:Y54	T54	=XLOOKUP(1,($R$44:$R$49=7.5)*($S$44:$S$49="yes"),T$44:T$49,)

 

[jrisebo]

thanks,
I expanded on this and figured a little more out along the lines of yours. Check this out, now the only time my values dont work is when my cell M26 is 7.5

Book2
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
25		Lower Angle:		Upper Angle:		Diff
26		0		7.5
27	Zone 1:	1.2	-1.1	2.6	-2.3	1.4	-1.2
28	Zone 2:	1.2	-1.1	2.6	-2.3	1.4	-1.2
29	Zone 3:	1.2	-1.1	2.6	-2.3	1.4	-1.2
30
31
32
33
34
35
36
37
38
39
40
41
42											Clear Wind Flow
43										yes	Zone 3		Zone 2		Zone 1
44								0no	0	no	2.4	-3.3	1.8	-1.7	1.2	-1.1
45								0no	0	no	1.8	-1.7	1.8	-1.7	1.2	-1.1
46								0yes	0	yes	1.2	-1.1	1.2	-1.1	1.2	-1.1
47								7.5no	7.5	no	3.2	-4.2	2.4	-2.1	1.6	-1.4
48								7.5no	7.5	no	2.4	-2.1	2.4	-2.1	1.6	-1.4
49								7.5yes	7.5	yes	1.6	-1.4	1.6	-1.4	1.6	-1.4
50								15no	15	no	3.6	-3.8	2.7	-2.9	1.8	-1.9
51								15no	15	no	2.7	-2.9	2.7	-2.9	1.8	-1.9
52								15yes	15	yes	1.8	-1.9	1.8	-1.9	1.8	-1.9
53								30no	30	no	5.2	-5	3.9	-3.8	2.6	-2.5
54								30no	30	no	3.9	-3.8	3.9	-3.8	2.6	-2.5
55								30yes	30	yes	2.6	-2.5	2.6	-2.5	2.6	-2.5
56								45no	45	no	5.2	-4.6	3.9	-3.5	2.6	-2.3
57								45no	45	no	3.9	-3.5	3.9	-3.5	2.6	-2.3
58								45yes	45	yes	2.6	-2.3	2.6	-2.3	2.6	-2.3
Sheet2

Cell Formulas
Range	Formula
K27	K27	=VLOOKUP(K26&$S$43,$Q$44:Y58,8)
L27,N27	L27	=VLOOKUP(K26&$S$43,$Q$44:Y58,9)
M27	M27	=VLOOKUP(M26&$S$43,$Q$44:Y58,8)
O27:P29	O27	=M27-K27
K28,M28	K28	=VLOOKUP(K26&$S$43,$Q$44:Y58,6)
L28,N28	L28	=VLOOKUP(K26&$S$43,$Q$44:Y58,7)
K29,M29	K29	=VLOOKUP(K26&$S$43,$Q$44:Y58,4)
L29,N29	L29	=VLOOKUP(K26&$S$43,$Q$44:Y58,5)
Q44:Q58	Q44	=CONCATENATE(R44&S44)

 

[jrisebo]

You need to change the last option in your VLOOKUP's to 0 or FALSE for exact match.

VLOOKUP(M26&$S$43,$Q$44:Y58,8,0)

 

[Peter_SSs]

If you didn't want to unmerge column R or individually enter the column of the lookup, you could consider something like this.
It assumes each of those sections in column S are the same (no, no, yes in this example)
Note that I have removed the ":" from column J. They could be put back in but the formula would become a bit longer.
The formula in K27 is copied across 1 column and down. Same for the formula in M27.

22 10 09.xlsm
	J	K	L	M	N			Q	R	S	T	U	V	W	X	Y
25		Lower Angle:		Upper Angle:
26		0		7.5
27	Zone 1	1.2	-1.1	1.6	-1.4
28	Zone 2	1.2	-1.1	1.6	-1.4
29	Zone 3	1.2	-1.1	1.6	-1.4
30
41
42											Clear Wind Flow
43										yes	Zone 3		Zone 2		Zone 1
44									0	no	2.4	-3.3	1.8	-1.7	1.2	-1.1
45										no	1.8	-1.7	1.8	-1.7	1.2	-1.1
46										yes	1.2	-1.1	1.2	-1.1	1.2	-1.1
47									7.5	no	3.2	-4.2	2.4	-2.1	1.6	-1.4
48										no	2.4	-2.1	2.4	-2.1	1.6	-1.4
49										yes	1.6	-1.4	1.6	-1.4	1.6	-1.4
50									15	no	3.6	-3.8	2.7	-2.9	1.8	-1.9
51										no	2.7	-2.9	2.7	-2.9	1.8	-1.9
52										yes	1.8	-1.9	1.8	-1.9	1.8	-1.9
53									30	no	5.2	-5	3.9	-3.8	2.6	-2.5
54										no	3.9	-3.8	3.9	-3.8	2.6	-2.5
55										yes	2.6	-2.5	2.6	-2.5	2.6	-2.5
56									45	no	5.2	-4.6	3.9	-3.5	2.6	-2.3
57										no	3.9	-3.5	3.9	-3.5	2.6	-2.3
58										yes	2.6	-2.3	2.6	-2.3	2.6	-2.3
Lookup

Cell Formulas
Range	Formula
K27:L29	K27	=INDEX($T$44:$Y$58,MATCH($K$26,$R$44:$R$58,0)+MATCH($S$43,$S$44:$S$58,0)-1,MATCH($J27,$T$43:$Y$43,0)+COLUMNS($K:K)-1)
M27:N29	M27	=INDEX($T$44:$Y$58,MATCH($M$26,$R$44:$R$58,0)+MATCH($S$43,$S$44:$S$58,0)-1,MATCH($J27,$T$43:$Y$43,0)+COLUMNS($K:K)-1)

 

[smozgur]

Although the VLOOKUP solution works fine, it requires an additional helper column. So I personally find Peter's solution much better than that. However, string concatenation is also widely used as a VLOOKUP helper method that might help future readers, so, I won't change the marked solution in this thread unless @jrisebo thinks Peter's solution is the way to go as well and mark it as the solution.