Double xlookup and dependent dropdowns = #N/A

[Learner007]

Help please
I have a simple double XLOOKUP and a simple dependent dropdown but something isn't working. I've tried to work it back but can't see what I'm doing wrong. I've searched and seen something N/A errors generate from formatting but I don't understand this really either.
Can someone help me?

doublexlookup.xlsx
	C	D	E	F	G	H	I	J
2	Arrays				Lists
3	Occupation	Band	Cost		Occupation	Doctor	Nurse	Scientific
4	Doctor	FY1	£45.00		Doctor	FY1	Band 4	Band 4
5	Doctor	FY2	£51.00		Nurse	FY2	Band 5	Band 5
6	Doctor	Registrar	£73.00		Scientific	Registrar	Band 6	Band 6
7	Doctor	Associate specialist	£137.00			Associate specialist	Band 7	Band 7
8	Doctor	Consultant medical	£145.00			Consultant medical	Band 8a	Band 8a
9	Nurse	Band 4	£39.00				Band 8b	Band 8b
10	Nurse	Band 5	£48.00				Band 8c	Band 8c
11	Nurse	Band 6	£59.00				Band 8d	Band 8d
12	Nurse	Band 7	£69.00
13	Nurse	Band 8a	£78.00
14	Nurse	Band 8b	£90.00
15	Nurse	Band 8c	£106.00		XLOOKUP x 2
16	Nurse	Band 8d	£125.00		Occupation	Band	Cost
17	Scientific	Band 4	£34.00		Doctor	FY2	#N/A
18	Scientific	Band 5	£41.00
19	Scientific	Band 6	£53.00
20	Scientific	Band 7	£64.00
21	Scientific	Band 8a	£73.00
22	Scientific	Band 8b	£86.00
23	Scientific	Band 8c	£102.00
24	Scientific	Band 8d	£121.00
Occs Bands and Costs

Cell Formulas
Range	Formula
I17	I17	=XLOOKUP(1,(Array_Occupation=G17)*(Array_Band=H17),Array_Occupation_cost)

Named Ranges
Name	Refers To	Cells
'Occs Bands and Costs'!Array_Band	='Occs Bands and Costs'!$D$4:$D$24	I17
'Occs Bands and Costs'!Array_Occupation	='Occs Bands and Costs'!$C$4:$C$24	I17
'Occs Bands and Costs'!Array_Occupation_cost	='Occs Bands and Costs'!$E$4:$E$24	I17

Cells with Data Validation
Cell	Allow	Criteria
G17	List	=IF(H17="",Occupation,INDIRECT("FakeList"))
H17	List	=INDIRECT(G17)

 

[RoryA]

You have trailing spaces on all the values in column C.

 

[Learner007]

Oh man. I can't believe I didn't see that. Thank you @RoryA

 

[Alex Blakenburg]

You can get away with this but of course it would be better to clean up your data:

=XLOOKUP(1,(TRIM(Array_Occupation)=G17)*(Array_Band=H17),Array_Occupation_cost)

 

[Learner007]

=XLOOKUP(1,(Array_Occupation=G17)*(Array_Band=H17),Array_Occupation_cost)"

Umm thanks but I don't understand what's changed. I must admit I found this formula on another website so aren't sure what I'm getting away with.

 

[RoryA]

Alex added a TRIM function to remove the trailing spaces.

 

[Learner007]

Boy I'd better go and get some stronger coffee. Thanks, @RoryA and @Alex Blakenburg