Change a Vertical Lookup table to one row

[rcb007]

I am trying to figure out how to take this vertical table to be all in one row, then make a formula to look it up. Thank you for any help!

Q Comparison.xlsx
	A	B	C	D	E	F
1	dia	min	Full	n	Q	Slope
2	12	0	1.78	0.013	5.20	0.25
3	15	1.78	3.23	0.013	5.20	0.25
4	18	3.23	5.25	0.013	5.20	0.25
5	21	5.25	7.92	0.013	5.20	0.25
6	24	7.92	11.31	0.013	5.20	0.25
7	30	11.31	20.51	0.013	5.20	0.25
8	36	20.51	33.35	0.013	5.20	0.25
9	42	33.35	50.30	0.013	5.20	0.25
10	48	50.30	71.82	0.013	5.20	0.25
11	54	71.82	98.32	0.013	5.20	0.25
12	60	98.32	130.22	0.013	5.20	0.25
13	66	130.22	167.90	0.013	5.20	0.25
14	72	167.90	211.75	0.013	5.20	0.25
15	dia	min	Full	n	Q	Slope
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=IF(SUM(A2:A2)=0,"-",(1.486/D2)*((A2^2)*PI()/576)*((A2/48)^(2/3))*((F2/100)^(1/2)))
B3:B14	B3	=C2

Vertical Lookup Forumula
=LOOKUP(I3,B2:B14,A2:A14)

Q Comparison.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ
18	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope	dia	min	Full	n	Q	Slope
19	12	0.00	1.78	0.01	5.20	0.25	15	1.78	3.23	0.01	5.20	0.25	18	3.23	5.25	0.01	5.20	0.25	21	5.25	7.92	0.01	5.20	0.25	24	7.92	11.31	0.01	5.20	0.25	30	11.31	20.51	0.01	5.20	0.25	36	20.51	33.35	0.01	5.20	0.25	42	33.35	50.30	0.01	5.20	0.25	48	50.30	71.82	0.01	5.20	0.25	54	71.82	98.32	0.01	5.20	0.25	60	98.32	130.22	0.01	5.20	0.25	66	130.22	167.90	0.01	5.20	0.25	72	167.90	211.75	0.01	5.20	0.25
Sheet1

Cell Formulas
Range	Formula
C19,BW19,BQ19,BK19,BE19,AY19,AS19,AM19,AG19,AA19,U19,O19,I19	C19	=(1.486/D19)*((A19^2)*PI()/576)*((A19/48)^(2/3))*((F19/100)^(1/2))
H19,BV19,BP19,BJ19,BD19,AX19,AR19,AL19,AF19,Z19,T19,N19	H19	=C19

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX(A19:BZ19,MATCH(I3,IF(A18:BZ18="min",A19:BZ19),1)-1)

Hope this helps!

 

[rcb007]

This helps out ALOT! ALOT.... Thank you so much!

 

[Domenic]

That's great, glad I could help, cheers!

 

[Peter_SSs]

A few comments/questions

needs to be confirmed with CONTROL+SHIFT+ENTER...

Since the OP has Microsoft 365, the C+S+E should not be required

@rcb007
Can you confirm what should happen if I3 is exactly equal to one of the range boundary points (eg For I3 = 5.25216917013281 should the answer be 18 or 21?)? Or this that not possible with your data?

Is it possible that I3 could be greater than the C14 value? If so the suggested formula would need a slight modification.

If interested, a couple of other formulas that I believe return the same values are

=MAX(FILTER(A19:BU19,(A18:BU18="dia")*(B19:BV19<=I3)))

=AGGREGATE(14,6,A19:BU19/((A18:BU18="dia")*(B19:BV19<=I3)),1)

 

[Domenic]

Since the OP has Microsoft 365, the C+S+E should not be required

Oh wow, I thought I checked for the version... I guess not. Thanks for catching it.

Cheers!

 

[rcb007]

By

A few comments/questions


Since the OP has Microsoft 365, the C+S+E should not be required

@rcb007
Can you confirm what should happen if I3 is exactly equal to one of the range boundary points (eg For I3 = 5.25216917013281 should the answer be 18 or 21?)? Or this that not possible with your data?

Is it possible that I3 could be greater than the C14 value? If so the suggested formula would need a slight modification.

If interested, a couple of other formulas that I believe return the same values are

=MAX(FILTER(A19:BU19,(A18:BU18="dia")*(B19:BV19<=I3)))

=AGGREGATE(14,6,A19:BU19/((A18:BU18="dia")*(B19:BV19<=I3)),1)

(eg For I3 = 5.25216917013281 should the answer be 18 or 21?)?

For an 18 (3.23 min and 5.25 max)
For an 21 (5.25 min and 7.92 max)

I would think the answer would be 21. I am looking at the 5.2521.... still being above the initial 5.25.

I hope that helps some.

 

[Peter_SSs]

For an 18 (3.23 min and 5.25 max)
For an 21 (5.25 min and 7.92 max)

I would think the answer would be 21. I am looking at the 5.2521.... still being above the initial 5.25.

I hope that helps some.

OK, that sounds like a value that is the upper limit of one range and the lower limit of the next range should return the second range. That is what all the suggested formulas do so all is good.

 

[rcb007]

Awesome, Thank you for taking the time to figure this out! Much Appreciated!

 

[Peter_SSs]

You're welcome. Glad we could help. Of course Domenic had already provided the solution as you noted earlier.