Nested Formula Issues

[janema]

I am having issues with figuring out the right way to write this formula. I am trying to get the formula to multiply the total in cell H4 (which is a currently an XLOOKUP formula) by the value in H2 if G4 equals "A4H" and to multiply the total in cell H4 by the value in cell I2 if G4 equals "TZZ." If it doesn't equal either, I want it just to stay as it is currently. Is this not possible because there is an XLOOKUP in that field currently? I've tried the following formulas and no luck, just getting errors.

=IF(G4="A4H",$H$1*H4,IF(G4="TZZ",$I$1*H4,H4)))

=IF(G4="A4H",$H$1*H4,IF(G4="TZZ",$I$1*H4,H4)))

=IF(G4="A4H",$H$1*XLOOKUP(A4,Census!C:C,Census!BT:BT),IF(G4="TZZ",$I$1*XLOOKUP(A4,Census!C:C,Census!BT:BT),XLOOKUP(A4,Census!C:C,Census!BT:BT)))))

Thank you so much in advance for your help!! Your amazing brain power is very much appreciated!

 

[MARK858]

What happens with the formula below? Please note I am using H1 and I1 as per your formula rather than H2 and I2 as per your question, amend if different

=IF(G4="A4H",($H$1+0)*H4,IF(G4="TZZ",($I$1+0)*H4,H4))

 

[janema]

What happens with the formula below? Please note I am using H1 and I1 as per your formula rather than H2 and I2 as per your question, amend if different

=IF(G4="A4H",($H$1+0)*H4,IF(G4="TZZ",($I$1+0)*H4,H4))

It returns "0" when I use that formula.

 

[MARK858]

Did you amend the H1 and I1 if different with your data as stated?

 

[janema]

Did you amend the H1 and I1 if different with your data as stated?

I did. It is H1 and I1 like you noticed.

 

[janema]

Did you amend the H1 and I1 if different with your data as stated?

I am thinking it has something to do with the XLOOKUP formula already in there.

 

[MARK858]

XLOOKUP itself probably wouldn't be the issue

Book1
	G	H	I	J	K	L	M	N	O	P	Q	R
1		4	8					b	d		a	2
2											b	4
3											c	6
4	A4H	2	3			8					d	8
Jan1

Cell Formulas
Range	Formula
H1:I1	H1	=XLOOKUP(N1,$Q$1:$Q$4,$R$1:$R$4)
L4	L4	=IF(G4="A4H",($H$1+0)*H4,IF(G4="TZZ",($I$1+0)*H4,H4))

What do you get with

=IF(G4="A4H",(TRIM($H$1)+0)*H4,IF(G4="TZZ",(TRIM($I$1)+0)*H4,H4))