Requesting data from table

[AllexDee]

Hi, the name is Alex and i am trying to reproduce a smart form (if you want to call it that).

Im working on a Pressure equipment directive calculator, yes im aware there is tools out there but due being for a corporate company a lot of software is banned so we have to make our own using excel.

The image below lets the user define the Pressure and volume of the asset/system.
then once these are set, the category should appear underneath from the table i have made as seen on image 2.
the formula i was using wasn't working properly and i forgot to save so i have no formula to share here. (Apologies).

I would love to have a chat with some one hoping they could help with a better way of designing this and maybe more straight forward.

Thankyou in advance and i do apologise if this is really basic.

 

[Skyybot]

It's pretty straight-forward. Could you paste the Worksheet here using the XL2BB Add-In?

 

[AllexDee]

It's pretty straight-forward. Could you paste the Worksheet here using the XL2BB Add-In?

it's not letting me use the add-in, says "this file is not supported in Protected view. I'm using my personal PC now for this.

 

[AllexDee]

It's pretty straight-forward. Could you paste the Worksheet here using the XL2BB Add-In?

PED Calculator.xlsm
	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
1
2
3
4		STEP 1								STEP 3												Bar (g) \ Volume (L)	Volume	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18
5																							11	12	13	14	15	16	17	19	20	22	25	28	33	40	50	65	100	200	400
6			Type of Equipment (drop down list)							Design Data (drop down list)												0.5	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP
7			Vessels							Design Pressure PS					10	Bar(g)						0.51	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1
8																						1	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1
9			Fluid Group (drop down list)							Volume (V)					12	L						2	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1
10			Other: 2																			3	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1
11										Category												4	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
12			Type of equipment (drop down list)																			5	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
13			Gas																			6	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
14																						7	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
15			Step 2 : Define Table																			8	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
16																						9	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
17																						10	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
18																						10.1	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
19																						11	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
20																						12	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
21																						13	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
22																						14	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
23																						15	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
24																						16	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
25																						17	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
26																						18	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
27																						19	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
28																						20	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
29
30
31
32
Sheet3

 

[Skyybot]

Book1
	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
1
2
3
4		STEP 1								STEP 3												Bar (g) \ Volume (L)	Volume	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18
5																							11	12	13	14	15	16	17	19	20	22	25	28	33	40	50	65	100	200	400
6			Type of Equipment (drop down list)							Design Data (drop down list)												0.5	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP
7			Vessels							Design Pressure PS					10	Bar(g)						0.51	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1
8																						1	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1
9			Fluid Group (drop down list)							Volume (V)					12	L						2	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1
10			Other: 2																			3	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1
11										Category					CAT 1							4	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
12			Type of equipment (drop down list)																			5	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
13			Gas																			6	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
14																						7	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
15			Step 2 : Define Table																			8	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
16																						9	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
17																						10	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
18																						10.1	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
19																						11	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
20																						12	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
21																						13	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
22																						14	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
23																						15	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
24																						16	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
25																						17	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
26																						18	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
27																						19	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
28																						20	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
29
30
31
32
Sheet1

Cell Formulas
Range	Formula
O11	O11	=VLOOKUP(O7,V6:AO28,O9)

 

[AllexDee]

Book1
	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
1
2
3
4		STEP 1								STEP 3												Bar (g) \ Volume (L)	Volume	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18
5																							11	12	13	14	15	16	17	19	20	22	25	28	33	40	50	65	100	200	400
6			Type of Equipment (drop down list)							Design Data (drop down list)												0.5	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP
7			Vessels							Design Pressure PS					10	Bar(g)						0.51	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1
8																						1	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1
9			Fluid Group (drop down list)							Volume (V)					12	L						2	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1
10			Other: 2																			3	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1
11										Category					CAT 1							4	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
12			Type of equipment (drop down list)																			5	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
13			Gas																			6	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
14																						7	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
15			Step 2 : Define Table																			8	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
16																						9	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
17																						10	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1	CAT 1
18																						10.1	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
19																						11	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
20																						12	SEP	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
21																						13	SEP	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
22																						14	SEP	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
23																						15	SEP	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
24																						16	SEP	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
25																						17	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
26																						18	SEP	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
27																						19	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
28																						20	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2	CAT 2
29
30
31
32
Sheet1

Cell Formulas
Range	Formula
O11	O11	=VLOOKUP(O7,V6:AO28,O9)

one issue is that if the pressure changes and volume changes its not giving out the right value with that

 

[AllexDee]

EDIT - 02.12.2024

Apologies if i didn't explain myself properly, What i need help with is that you go down the pressure collum and whatever that may be, let's go with 10 bar in this case, you then you go across the volume row and find what you're looking for (22 litres for volume), once that has been done you cross them both to where they meet and that should pop up in the category cell, and you should get Cat 1.

 

[Fluff]

How about

=INDEX(W6:AO28,XMATCH(O7,V6:V28),XMATCH(O9,W5:AO5))

 

[AllexDee]

How about

=INDEX(W6:AO28,XMATCH(O7,V6:V28),XMATCH(O9,W5:AO5))

that seems to have not given me any issues, but its coming up as N/A.

 

[Fluff]

Check that all your numbers are real numbers & not text.