HLOOKUP returns #N/A, but the value is there

[Alonshow]

Hi, I'm struggling with some formulas that use HLOOKUP to retrieve values from a table. I have checked that the reference values I give to HLOOKUP are correct, and that those values are present in the data table, but my formulas return #N/A even though everything seems to be correct.

Context: I believe this is an unusual case scenario for Excel. I have some mental health problems that heavily affect my day-to-day routine, and I use an Excel workbook to monitor my daily activity and assess whether I am making progress. The core of the workbook is a set of formulas that read data about my activity during the day and return a number that tells me how good/bad that day was. For instance, I want to follow about a dozen healthy daily habits every day (like eating vegs, taking my medication, doing some exercise, etc). One of the formulas counts how many of these I follow each day. If I run that formula for yesterday and the result is 11, that means that yesterday I was very consistent with my daily habits. If I get 5 it means there's room for improvement. And if I get 1 it means it was a disaster.

This particular formula looks like this:
=IF(ISNUMBER(Day_1), HLOOKUP(Day_1,Activities_2022B,19,FALSE), "")

This formula refers to the table Activities_2022B, which contains a list of my healthy habits for each day of the year. Row 19 of that table is a sum of the number of daily habits I have performed in a particular day.

These formulas have been working well for several years. However, one month ago I decided to do a bit of clean-up of the workbook because it had been growing so much that it was starting to be difficult to manage. As part of the clean-up, I used the Name manager to remove tables that didn't seem to be in use, and also to rename tables that are in use to make their names more consistent. At first, it seemed to work well, but the next time I tried to put the worksheet to actual use, lots of #N/A errors started to show up all over the place.

Now, when I try to rename the tables in the sheet, I get some weird error messages. For instance, if I try to rename the table Activities_2022B to Activities_2022 (without the B), Excel says that's not possible because there is another table called Activities_2022. However, that other table doesn't show up in the Name Manager or anywhere else.

I attach the range where my data is located, which is a part of the Activities_2022B table (the whole table is too big to post here).

Forum sample.xlsx
	LY	LZ	MA	MB	MC	MD	ME	MF	MG	MH	MI	MJ	MK	ML	MM	MN	MO	MP	MQ	MR	MS	MT	MU	MV	MW	MX	MY	MZ	NA	NB	NC
1	Thu, 01-Dec	Fri, 02-Dec	Sat, 03-Dec	Sun, 04-Dec	Mon, 05-Dec	Tue, 06-Dec	Wed, 07-Dec	Thu, 08-Dec	Fri, 09-Dec	Sat, 10-Dec	Sun, 11-Dec	Mon, 12-Dec	Tue, 13-Dec	Wed, 14-Dec	Thu, 15-Dec	Fri, 16-Dec	Sat, 17-Dec	Sun, 18-Dec	Mon, 19-Dec	Tue, 20-Dec	Wed, 21-Dec	Thu, 22-Dec	Fri, 23-Dec	Sat, 24-Dec	Sun, 25-Dec	Mon, 26-Dec	Tue, 27-Dec	Wed, 28-Dec	Thu, 29-Dec	Fri, 30-Dec	Sat, 31-Dec
2	5 PM	11 PM	10 PM		1 AM	2 AM	3 AM	5 AM	2 AM	3 AM	4 AM	5 AM	6 AM	7 AM	8 AM	1 PM	12 PM		12 PM	12 PM	3 PM	4 PM	3 PM	5 AM	5 AM	4 AM	3 PM		2 AM	4 AM	1 AM
3	4 PM	4 PM	5 PM		6 PM	10 PM	8 PM	4 PM	5 PM	8 PM	10 PM	11 PM	10 PM	10 PM	6 AM	5 AM		3 AM	3 AM	7 AM	11 AM	7 AM	12 AM	11 PM	11 PM	5 AM		3 PM	7 PM	5 PM	3 PM
4	8	7	6	0	8	8	5	9	10	10	8	7	7	9	10	7	7	0	9	9	8	5	8	5	6	5	10	0	11	9	8
5	x		x		x	x	x	x	x				x		x		x			x	x	x		x		x	x		x	x	x
6																	x
7	x				x	x	x	x	x						x		x			x	x	x		x		x	x		x	x	x
8	x				x	x	x	x	x						x		x			x	x	x		x		x	x		x	x	x
9	x	x	x		x	x	x	x	x	x	x	x	x		x	x	x		x	x	x	x	x	x	x	x	x		x	x	x
10			x						x
11	Walking								Walking
12
13
14	x	x							x	x					x	x						x	x	x	x
15	Melanie								Mónica						Merce							Lang exch		Merce
16	Melanie			Jáled										Fundación Anar	Merce							Lang exch		Merce
18	x	x	x		x	x	x		x		x	x		x	x	x			x	x	x	x	x	x	x	x	x		x	x	x
19	9	3	4	1	5	5	5	4	9	2	2	2	2	2	8	3	5		2	5	5	8	3	8	3	5	5		5	5	5
21
22									Mónica													Lang exch
23				Jáled											Merce							Lang exch		Merce
24
25															x									x	x	x
26	Melanie														Merce									Merce
27
28									Mónica
29	-2			1					-2						-1							3		-1	0	0
Routine tasks 2022

Cell Formulas
Range	Formula
LY4:NC4	LY4	=HOUR(IF(AND(ISNUMBER(LY2), ISNUMBER(LX3)), IF(LY2>LX3,LY2-LX3, (1+LY2)-LX3), IF(ISNUMBER(LY2),IF(LY2>LW3,LY2-LW3, (1+LY2)-LW3), 0)))
LY19:NC19	LY19	=IF(COUNTA(LY5:LY18)>0, COUNTA(LY5:LY18),"")
LY29:NC29	LY29	=IF(COUNTA(LY21:LY28)=0, "", 2*COUNTA(LY21) + 2*COUNTA(LY22) + COUNTA(LY23) - 2*COUNTA(LY26) - 3*COUNTA(LY27) - 4*COUNTA(LY28) + IF(COUNTA(LY24)=0, 0, 8-LY4))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:XFD4	Cell Value	=0	text	NO
C4:NC4	Cell Value	between 11 and 500	text	NO

Thank you in advance for any help.

 

[Anthony47]

A structured table is a range of cells declared to be an entity with some internal organization. For example, table Headers can be used to refers to the table column datas.
When you create a Table (Menu /Insert /Table) the table is assigned a name, that is also a named range visible via the Formula /Name manager; you cannot modify the “Refers to” box

For how to Create and format tables:

Create and format tables - Microsoft Support

Learn about the many ways to create a table in an Excel worksheet, and how to clear data, formatting, or table functionality from a table in Excel. Discover more in this video.

support.microsoft.com

For how the elements in a table can be addressed:

Using structured references with Excel tables - Microsoft Support

Structured references make it easier to use formulas with Excel tables by replacing cell references, such as C2:C7, with predefined names for the items in a table.

support.microsoft.com

If your range turned to be a structured table, then if you go to a free cell, type = (to start a formula) then with the mouse you click on C1 (to insert the formula =C1) then the formula you see in the formula bar should look like =TableName[[#Headers],[HeaderValue]]
Also if you select any cell in the area then in the ribbon a new tab should be displayed, named “Table Structure” (or something similar)

Some of your symptoms tell me that your range might be a structured table; but such a table should have peculiar formatting elements that I don’t see

Maybe by having access to your real workbook we could go to the source of the problem; at the moment I am afraid we have to settle for the Offset solution.
I should however suggest that you recreate your workbook from scratch, moving the data from your current file to the new one using Copy / Paste special – Values only; at that point the confusion that is in your current workbook should have gone away