Some of the equation works?

[preifenstein]

Trying to make this work and I can't see what is wrong.

Equation in column "I" pulls the data, but only partially. Won't pull data from Sheet 3 if it's Sheet 3, C8 or C9. I can't figure it out.

Invoice Doc - Test.xlsx
	H	I
1	Meal Plan Fees - June 15	Meal Plan Fees - August 1
2	$ 1,250.00	$ 1,250.00
3	$ 2,500.00	$ -
4	$ 2,800.00	$ -
5	$ 2,500.00	$ -
6	$ 2,800.00	$ -
7	$ 1,700.00	$ 1,700.00
8	$ 1,250.00	$ 1,250.00
9	$ -	$ -
10	$ 3,100.00	$ -
11	$ 2,800.00	$ -
12	$ 1,250.00	$ 1,250.00
13	$ -	$ -
14	$ 2,800.00	$ -
15	$ 2,500.00	$ -
16	$ 2,800.00	$ -
17	$ -	$ -
18	$ 2,800.00	$ -
19	$ -	$ -
20	$ -	$ -
21	$ -	$ -
22	$ 2,800.00	$ -
23	$ 1,250.00	$ 1,250.00
24	$ 2,800.00	$ -
25	$ 1,250.00	$ 1,250.00
26	$ -	$ -
27	$ 2,500.00	$ -
28	$ 2,500.00	$ -
29	$ 2,500.00	$ -
30	$ 1,250.00	$ 1,250.00
Sheet1

Cell Formulas
Range	Formula
H2:H30	H2	=IF(Sheet2!$U2="Mohawk Commuter Full Plan Academic Year 2021-2022",Sheet3!$B$7,IF(Sheet2!$U2="Mohawk Classic Full Plan Academic Year 2021-2022",Sheet3!$B$8,IF(Sheet2!$U2="Mohawk WKND WAR Full Plan Academic Year 221-2022",Sheet3!$B$9,IF(Sheet2!$U2="Mohawk Ult Flex Full Plan Academic Year 2021-2022",Sheet3!$B$10,$I2))))
I2:I30	I2	=IF(Sheet2!$U2="Mohawk Commuter Split Plan Academic Year 2021-22",Sheet3!$C$7,IF(Sheet2!$U2="Mohawk Classic Split Plan Academic Year 2021-2022",Sheet3!$C$8,IF(Sheet2!$U2="Mohawk WKND WAR Split Plan Academic Year 221-2022",Sheet3!$C$9,IF(Sheet2!$U2="Mohawk Ult Flex Split Plan Academic Year 2021-2022",Sheet3!$C$10,0))))

Invoice Doc - Test.xlsx
	U
2	Mohawk Commuter Split Plan Academic Year 2021-22
3	Mohawk Commuter Full Plan Academic Year 2021-2022
4	Mohawk Classic Full Plan Academic Year 2021-2022
5	Mohawk Commuter Full Plan Academic Year 2021-2022
6	Mohawk Classic Full Plan Academic Year 2021-2022
7	Mohawk Ult Flex Split Plan Academic Year 2021-2022
8	Mohawk Commuter Split Plan Academic Year 2021-22
9	Mohawk Classic Split Plan Academic Year 2021-2022
10	Mohawk WKND WAR Full Plan Academic Year 221-2022
11	Mohawk Classic Full Plan Academic Year 2021-2022
12	Mohawk Commuter Split Plan Academic Year 2021-22
13	Mohawk Classic Split Plan Academic Year 2021-2022
14	Mohawk Classic Full Plan Academic Year 2021-2022
15	Mohawk Commuter Full Plan Academic Year 2021-2022
16	Mohawk Classic Full Plan Academic Year 2021-2022
17	Mohawk Classic Split Plan Academic Year 2021-2022
18	Mohawk Classic Full Plan Academic Year 2021-2022
19	Mohawk Classic Split Plan Academic Year 2021-2022
20	Mohawk Classic Split Plan Academic Year 2021-2022
21	Mohawk Classic Split Plan Academic Year 2021-2022
22	Mohawk Classic Full Plan Academic Year 2021-2022
23	Mohawk Commuter Split Plan Academic Year 2021-22
24	Mohawk Classic Full Plan Academic Year 2021-2022
25	Mohawk Commuter Split Plan Academic Year 2021-22
26	Mohawk Classic Split Plan Academic Year 2021-2022
27	Mohawk Commuter Full Plan Academic Year 2021-2022
28	Mohawk Commuter Full Plan Academic Year 2021-2022
29	Mohawk Commuter Full Plan Academic Year 2021-2022
30	Mohawk Commuter Split Plan Academic Year 2021-22
Sheet2

Invoice Doc - Test.xlsx
	A	B	C	D
1	Residence Fees	June 15	September 15	November 15
2	Option 1	$8,167.00
3	Option 2	$2,886.00	$5,406.00
4	Option 3	$2,911.00	$2,352.00	$3,054.00
5
6	Meal Plan Fees	Option 1	Option 2
7	Commuter	$2,500.00	$1,250.00
8	Classic	$2,800.00	$1,400.00
9	Weekend Warrior	$3,100.00	$1,550.00
10	Ultimate Flex	$3,400.00	$1,700.00
Sheet3

 

[jasonb75]

Some of the cells in column U have an extra space at the end of the text, which will cause a mismatch.

You either need to remove those spaces or wrap each reference to column U in the TRIM() function, e.g. TRIM(Sheet2!$U2)

Although, it would be better if you had the same text in both sheet 3 and sheet 2 so that you could use a lookup table instead of a lot of nested IF's that can easily go wrong.

 

[Fluff]

It won't pull from C9 as you don't have this "Mohawk WKND WAR Split Plan Academic Year 221-2022" in sheet2.
For C8, U9 has a space at the end of it.

 

[preifenstein]

Thank you for your help. I would not have noticed that. I appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback.