xlookup and index not working properly

[curiouscoder]

so this is my first table

"Assigned" column formula

=XLOOKUP(F3&H3,Table2[Customer ID]&Table2[New Status],Table2[Date + Time],,,-1)

Customer ID - F	Entity Type - G	Status - H	Pod (Team) - I	Reviewer - J	Assigned -K
r3r5r5r3r		3 - Escalated Pre CC			#N/A​
t45t45t4y		1 - Assigned			#N/A​
434trr45t		2 - In Progress			30/06/2022 16:55:55​
45t45t		1 - Assigned			30/06/2022 16:34:56​
46y45					30/06/2022 15:32:59​
34t45ty					30/06/2022 12:44:42​
45y3					30/06/2022 12:44:43​
34ty46					30/06/2022 13:38:33​
4y45					30/06/2022 12:44:44​
5t4545t					#N/A​
y78uu8					#N/A​
i9i98o					#N/A​
o80o6					#N/A​
t45t45t4y					#N/A​
5y676u					#N/A​
y45y46					#N/A​
t4t4					#N/A​
t45y6					#N/A​
5y6y					29/06/2022 17:47:03​
y5y56					29/06/2022 17:47:01​
y5y6		1 - Assigned			30/06/2022 13:39:24​
5y56y					29/06/2022 17:48:05​
asfkkl3j4n		1 - Assigned			#N/A​
1abcd		1 - Assigned			#N/A​
hello		1 - Assigned			#N/A​
4t45t4y		1 - Assigned			30/06/2022 16:41:33​
4t45t4y		1 - Assigned			30/06/2022 16:41:33​

"Cell Row" column formula

=IFERROR(SUBSTITUTE(SUBSTITUTE([@[Cell Changed]],"H",""),"$",""),[@[Cell Changed]])

"Customer ID" column formula

= INDEX(Table1[Customer ID],[@[Cell Row]])

the table was longer but I just copied a segment of it

Date + Time	Change type	Cell Changed	Old Status	New Status	Customer ID	Cell Row	Date	Time
30/06/2022 16:28​	Cell Change	$E$26:$AH$26			#VALUE!​	E26:A26	30/06/2022​	16:28​
30/06/2022 16:28​	Cell Change	$H$26		1 - Assigned	4t45t4y	26	30/06/2022​	16:28​
30/06/2022 16:29​	Cell Change	$H$3		1 - Assigned	434trr45t	3	30/06/2022​	16:29​
30/06/2022 16:29​	Cell Change	$H$3		2 - In Progress	434trr45t	3	30/06/2022​	16:29​
30/06/2022 16:34​	Cell Change	$H$4		1 - Assigned	45t45t	4	30/06/2022​	16:34​
30/06/2022 16:35​	Cell Change	$H$5		1 - Assigned	46y45	5	30/06/2022​	16:35​
30/06/2022 16:35​	Cell Change	$H$5		2 - In Progress	46y45	5	30/06/2022​	16:35​
30/06/2022 16:39​	Cell Change	$H$6		1 - Assigned	34t45ty	6	30/06/2022​	16:39​
30/06/2022 16:41​	Cell Change	$E$27:$AH$27	1 - Assigned		#VALUE!​	E27:A27	30/06/2022​	16:41​
30/06/2022 16:41​	Cell Change	$H$27		1 - Assigned	4t45t4y	27	30/06/2022​	16:41​
30/06/2022 16:52​	Cell Change	$E$28:$AH$28	1 - Assigned		#VALUE!​	E28:A28	30/06/2022​	16:52​
30/06/2022 16:52​	Cell Change	$H$28		1 - Assigned	#REF!​	28	30/06/2022​	16:52​
30/06/2022 16:55​	Cell Change	$H$3	2 - In Progress	2 - In Progress	434trr45t	3	30/06/2022​	16:55​
30/06/2022 16:55​	Cell Change	$H$3	2 - In Progress	1 - Assigned	434trr45t	3	30/06/2022​	16:55​
30/06/2022 16:55​	Cell Change	$H$3	2 - In Progress	2 - In Progress	434trr45t	3	30/06/2022​	16:55​
30/06/2022 16:55​	Cell Change	$H$3	2 - In Progress	3 - Escalated Pre CC	434trr45t	3	30/06/2022​	16:55​
30/06/2022 17:16​	Cell Change	$E$29:$AH$29			#VALUE!​	E29:A29	30/06/2022​	17:16​
30/06/2022 17:16​	Cell Change	$H$29		1 - Assigned	#REF!​	29	30/06/2022​	17:16​

so the way my tables work is my second table is a changelog that automatically generates from date+time to status via some vba code i found on the internet and the remaining columns ive made on my own. my aim is for the first table to successfuly grab the date+time from the second table using columns F and H and entered in "Assigned" on the first table. I dont know why im getting errors in both tables.

i.e. I dont know why im gettin #REF! errors when using INDEX as well and i dont know how to fix it

also when using x lookup i only want it returning a value when both columns in F and H are populated. as you can see some rows in the status column are blank yet a result is given.

any help would be really appreciated.

this was all working fine before when using if statements and now statements to generate time stamps but the boss told me to redo 4 days of work because he was getting circular errors on his end and didnt want himself and other users to turn on iterative calculations

 

[curiouscoder]

also I dont know why this is giving me a circular error

=IF(H3='DROPDOWN CATS'!$A$8,XLOOKUP(F3&H3,Table2[Customer ID]&Table2[New Status],Table2[Date + Time],,,-1),IF(H3="","",K3))

 

[KRice]

You are getting the #REF! errors in the above example because you are attempting to return row indexes 28 and 29, but Table1 stops at row index 27. The #VALUE! errors are caused by attempting to pass a multi-column cell range to INDEX when it expects a number or an array of numbers (and the range provided doesn't contain numbers)...what is intended by E26:A26 in the 1st data cell of the [Cell Row] column? There appears to be a problem with your [Cell Row] formula because it does not consistently return a number.

 

[curiouscoder]

You are getting the #REF! errors in the above example because you are attempting to return row indexes 28 and 29, but Table1 stops at row index 27. The #VALUE! errors are caused by attempting to pass a multi-column cell range to INDEX when it expects a number or an array of numbers (and the range provided doesn't contain numbers)...what is intended by E26:A26 in the 1st data cell of the [Cell Row] column? There appears to be a problem with your [Cell Row] formula because it does not consistently return a number.

E26:A26 happens when I inset a new row


What can I adjust in my index formula to fix it? As I am unsure

 

[KRice]

I don't understand what you mean by "E26:A26 happens". Are you saying that when you insert a new row in Table1, then something automatically appears in the new row in columns A:E? What does that look like?

 

[curiouscoder]

I don't understand what you mean by "E26:A26 happens". Are you saying that when you insert a new row in Table1, then something automatically appears in the new row in columns A:E? What does that look like?

Yes, sorry for my poor explanation. When I insert a new row into the table, the second table (changelog) records it as "E26:A26" etc

My table starts at column F so I'm guessing the macro used to record changes into the changelog records all rows and columns on the left hand side of the table

 

[KRice]

On your spreadsheet, what do you see when you go to an empty area and type =A26:E26 (be sure that there are 4 empty cells to the right of wherever you type this...I'm trying to understand what is there).

 

[curiouscoder]

On your spreadsheet, what do you see when you go to an empty area and type =A26:E26 (be sure that there are 4 empty cells to the right of wherever you type this...I'm trying to understand what is there).

As the spreadsheet was on my work device I've been signed out and will only get access tomorrow.

However any help on my other queries is possible would be greatly appreciated