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]

I guess your lookup row is 1, that is a "Date".
Assuming this is correct, what type of information is in "DAY_1"? Is it a Date (if you format that cell as Number with three decimals it should show as an Integer, example 44905.000) or a String (reformatting has no effect)?

 

[Alonshow]

Yes, the lookup row is 1 and both Day_1 and the values in that row are dates (formatting them as numbers does show an Integer).

 

[Anthony47]

On a free worksheet, insert in A1, A2, A3, A4 and A5 the following formulas:

=INDEX(Activities_2022B,1,0)

=ROWS(Activities_2022B)

=COLUMNS(Activities_2022B)

=ROW(INDEX(Activities_2022B,1,1))

=COLUMN(INDEX(Activities_2022B,1,1))

Then please show what you get (a screenshot of the first 10 columns should be sufficient)

 

[Alonshow]

OK, I just copied and pasted your formulas in a new sheet in my workbook. Hope that's what you meant. This is the result:

 

[Anthony47]

Ok
Those results demonstrated that the named range Activities_2022B starts from B2, and in row 1 times (not dates) are shown; I guess it should start B1.
So you need to go to Name Manager and adjust the "Refers to" area for that named range

Try...

 

[Alonshow]

Yeah, I realized that's the problem just as I received your reply. Thank you. I feel a bit embarrassed that it was something so simple, but truth is, Excel's UI is a bit confusing in that sense. When the whole table is selected, visually it looks like the first row is a part of the selection, even though it is not. I guess that's what's kept me confused all this time.

The only thing now is that the name manager won't let me change the range of the table. When I select the table and click Edit, I get a dialogue that displays the range of the table greyed out and doesn't allow me to change it. Table design -> Resize table doesn't work either, it says the first row in the table must remain in the same row. Maybe I could just delete the table and create it again, but I wonder if that might create more problems than it solves... Any ideas?

 

[Anthony47]

So that range is indeed a "structured table" (ie an area declared as table, not only an ordered collection of rows and columns), and the dates are the headers of the table?
Probably the smart way would be using MATCH with the range Activities_2022B[#Headers] to determine the column and than INDEX.
But the easiest way should be using OFFSET in your original formula:

=IF(ISNUMBER(Day_1), HLOOKUP(Day_1,OFFSET(Activities_2022B,-1,0),19,FALSE), "")

Check if Index=19 is correct or need to be 20

 

[Anthony47]

Please read the above message, but first check if this formula works:
=IF(ISNUMBER(Day_1), HLOOKUP(Day_1,Activities_2022B[#All],19,FALSE), "")

 

[Alonshow]

the easiest way should be using OFFSET in your original formula:
=IF(ISNUMBER(Day_1), HLOOKUP(Day_1,OFFSET(Activities_2022B,-1,0),19,FALSE), "")

Worked! Thank you so much! So this solves the problem. The only thing is that this adds some complexity to the formula, so it would be even better if I could somehow redefine the table to include the dates/headers. But if that's not possible and/or too complicated, this is still a great solution

check if this formula works:
=IF(ISNUMBER(Day_1), HLOOKUP(Day_1,Activities_2022B[#All],19,FALSE), "")

For the record, this didn't work, returns #N/A.

So that range is indeed a "structured table" (ie an area declared as table, not only an ordered collection of rows and columns), and the dates are the headers of the table?

Also for the record, I don't understand this question. My knowledge of Excel is limited. Originally it was just a set of data, and at some point I decided to give it a name to make my formulas more manageable. I did think that the dates were the headers, but now that I've found out that the headers are not a part of the table, I'm not really sure. Here's a screenshot of the beginning of the table, in case it helps: