Help with a unit convert table with formulas and multiple index/match criteria

[StrawberryDreams]

I've built a table that converts the value based on the Original Unit and the Converted to Unit. The New cell value relies on another table that has the multiple possibilities and results that are used to pull from using index match.

The Element table allows the user to select an Element and its Value. As it stands everything works but I don't know how to expand the Element table to do another conversion for another element without getting a spill error ( Convert Table would have to have each of the 9 possible formula results written for each possible Element Name). I could do that but would like to learn if there is a better more elegant way to do this even in VBA if that makes it easier ?

Basic data calculator test 9.9b.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Original Unit					Element	Atomic Weight	Valence			mmol/L = ppm / atomic weight
2	mmol/L					N	14	1			meq/L = ppm * Valence / Atomic Weight
3	Convert to Unit					Ca	30	2			ppm = meq/L * Atomic Weight / Valence
4	meq/L
5						Convert Table
6						Element Choosen	Original Unit	Convert to Unit	New Value Result		Convert formulas for New Value Result
7						Ca	ppm	ppm	120		No conversion needed - Units match
8	Element	Value	New Value			Ca	ppm	mmol/L	4		mmol/L = Cell Value * Atomic weight
9	Ca	120	240			Ca	ppm	meq/L	8		meq/L = (Cell Value * Valence) / Atomic Weight
10
11						Ca	mmol/L	mmol/L	120		No conversion needed - Units match
12						Ca	mmol/L	ppm	3600		ppm = Cell Value * Atomic Weight
13						Ca	mmol/L	meq/L	240		meq/L = Cell Value * Valence
14
15						Ca	meq/L	meq/L	120		No conversion needed - Units match
16						Ca	meq/L	ppm	1800		ppm = Cell Value * Atomic Weight / Valence
17						Ca	meq/L	mmol/L	60		mmol/L = Cell Value / Valence
Sheet1

Cell Formulas
Range	Formula
C9	C9	=INDEX(Table26[New Value Result],MATCH(1,(Table26[Original Unit]=A2)*(Table26[Convert to Unit]=A4),0))
F7:F8,F15:F17,F11:F13	F7	=Table24[Element]
F9	F9	=Table24[@Element]
I7,I15,I11	I7	=Table24[Value]
I8	I8	=Table24[Value] / INDEX(Table25[Atomic Weight],MATCH(Table24[Element],Table25[Element],0))
I9	I9	=Table24[Value] * INDEX(Table25[Valence],MATCH(Table24[@Element],Table25[Element],0)) / (INDEX(Table25[Atomic Weight],MATCH(Table24[Element],Table25[Element],0)))
K11,K15	K11	=K7
I12	I12	=Table24[Value]* INDEX(Table25[Atomic Weight],MATCH(Table24[Element],Table25[Element],0))
I13	I13	=Table24[Value] * INDEX(Table25[Valence],MATCH(Table24[Element],Table25[Element],0))
I16	I16	=Table24[Value] * INDEX(Table25[Atomic Weight],MATCH(Table24[Element],Table25[Element],0)) / INDEX(Table25[Valence],MATCH(Table24[Element],Table25[Element],0))
I17	I17	=Table24[Value] / INDEX(Table25[Valence],MATCH(Table24[Element],Table25[Element],0))

Cells with Data Validation
Cell	Allow	Criteria
A4:A5	List	ppm, mmol/L, meq/L
A2	List	ppm, mmol/L, meq/L
A9	List	N,Ca

 

[StrawberryDreams]

Sorry, I have no idea.

No problem. I appreciate you taking a crack at it. Thank you.

 

[StrawberryDreams]

See if this works for you.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Original Unit		Element	Atomic Weight	Valence		Element	Original	Covert	Value	New Value
2	ppm		Total N	14.0067	1		Ca	ppm	ppm	120	120
3	Convert to Unit		NO3-	14.0067	1		Ca	ppm	mmol/L	100	4008
4	meq/L		NH4	14.0067	1		Ca	ppm	meq/L	120	6
5			P	30.973762	1		Ca	mmol/L	mmol/L	100	100
6			K	39.0983	1		Ca	mmol/L	ppm	120	4809
7			S	32.065	2		P	mmol/L	meq/L	100	100
8			Ca	40.078	2		Ca	meq/L	meq/L	120	120
9			Mg	24.305	2		Mg	meq/L	ppm	100	1215
10			Fe	55.845	2		Ca	meq/L	mmol/L	120	60
11			Mn	54.938044	2
12			B	10.811	2
13			Cu	63.546	2
14			Zn	65.38	2
15			Mo	95.95	1
16			Cl	35.453	1
17			Si	28.0855	4
18			Co	58.933195	1
19			Na	22.989769	1
20			Ni	58.6934	1
Sheet13

Cell Formulas
Range	Formula
K2:K10	K2	=LET(w,XLOOKUP(G2,$C$2:$C$20,$D$2:$D$20),v,XLOOKUP(G2,$C$2:$C$20,$E$2:$E$20), oldval,J2, SWITCH(H2&I2,"ppmmmol/L",oldval*w,"ppmmeq/L",(oldval*v)/w,"mmol/Lppm",oldval*w,"mmol/Lmeq/L",oldval*v,"meq/Lppm",oldval*w/v,"meq/Lmmol/L",oldval/v,oldval))

Cool , will try it out . Looks like some of the manual conversion formulas are moved around and giving different results - but seems to be working. I'll delve into the switch part of your formula and see if i can rearrange the math to get the conversions. Cheers

 

[StrawberryDreams]

See if this works for you.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Original Unit		Element	Atomic Weight	Valence		Element	Original	Covert	Value	New Value
2	ppm		Total N	14.0067	1		Ca	ppm	ppm	120	120
3	Convert to Unit		NO3-	14.0067	1		Ca	ppm	mmol/L	100	4008
4	meq/L		NH4	14.0067	1		Ca	ppm	meq/L	120	6
5			P	30.973762	1		Ca	mmol/L	mmol/L	100	100
6			K	39.0983	1		Ca	mmol/L	ppm	120	4809
7			S	32.065	2		P	mmol/L	meq/L	100	100
8			Ca	40.078	2		Ca	meq/L	meq/L	120	120
9			Mg	24.305	2		Mg	meq/L	ppm	100	1215
10			Fe	55.845	2		Ca	meq/L	mmol/L	120	60
11			Mn	54.938044	2
12			B	10.811	2
13			Cu	63.546	2
14			Zn	65.38	2
15			Mo	95.95	1
16			Cl	35.453	1
17			Si	28.0855	4
18			Co	58.933195	1
19			Na	22.989769	1
20			Ni	58.6934	1
Sheet13

Cell Formulas
Range	Formula
K2:K10	K2	=LET(w,XLOOKUP(G2,$C$2:$C$20,$D$2:$D$20),v,XLOOKUP(G2,$C$2:$C$20,$E$2:$E$20), oldval,J2, SWITCH(H2&I2,"ppmmmol/L",oldval*w,"ppmmeq/L",(oldval*v)/w,"mmol/Lppm",oldval*w,"mmol/Lmeq/L",oldval*v,"meq/Lppm",oldval*w/v,"meq/Lmmol/L",oldval/v,oldval))

it was just the first formula was a * instead of a /. All good now . I probably wrote the original formula incorrectly.

Much better then copying 9 possibilities of code into 170 rows in a table. thanks again!

 

[Dave Patton]

You stated "it was just the first formula was a * instead of a /. All good now

Please show your edits and comparison to expected results.
N.B.
I am not familiar with these conversion; I just thought the results should be logical. I completed minimal tests.
You can name the new function in Name Manager and show the parameters.

Draft