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.

 

[AllexDee]

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

kinda fixed, i had to add a 1 after =INDEX(W6:AO28,XMATCH(O7,V6:V28,1),XMATCH(O9,W5:AO5,1)) but now some bits dont work, for example 400 for volume and 0.51 for the pressure comes down to SEP rather than 1. so not sure what i did there to change the formula

 

[Fluff]

Oops, II put the two cells the wrong way, it should be

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

 

[AllexDee]

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

I fixed it!, thankyou for helping massively! instead of Xmatch i did just Match - =INDEX(W6:AO28,MATCH(O7,V6:V28),MATCH(O9,W5:AO5))

 

[Fluff]

That formula won't work, see post#12

 

[AllexDee]

That formula won't work, see post#12

That is odd, because it's working

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.00	12.00	13.00	14.00	15.00	16.00	17.00	19.00	20.00	22.00	25.00	28.00	33.00	40.00	50.00	65.00	100.00	200.00	400.00
6			Type of Equipment (drop down list)							Design Data (drop down list)												0.50	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP
7			Vessels							Design Pressure PS					12.00	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.00	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)					11.00	L						2.00	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.00	SEP	SEP	SEP	SEP	SEP		SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	SEP	CAT 1	CAT 1	CAT 1	CAT 1
11										Category					SEP							4.00	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.00	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.00	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.00	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.00	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.00	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.00	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.10	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.00	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.00	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.00	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.00	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.00	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.00	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.00	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.00	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.00	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.00	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
33
34
35
36
37
38
39
40
41
42
43
44
Sheet3

Cell Formulas
Range	Formula
O11	O11	=INDEX(W6:AO28,MATCH(O7,V6:V28),MATCH(O9,W5:AO5))

 

[Fluff]

Try changing O9 to 10.1

 

[AllexDee]

Try changing O9 to 10.1

but O9 is the volume which is the row on top, I would have to change O7 as that's pressure.

have we mixed our communication? as I saw previously you mistyped the formula but you didn't, you actually helped me and got the formula correct :P

 

[Fluff]

If you're happy, that's all that matters.

 

[AllexDee]

If you're happy, that's all that matters.

Honestly, thankyou! this was a trial, I can now upscale this a lot higher thanks to you.

 

[Fluff]

Glad to help & thanks for the feedback.