Lookup Formula Error (but for only 1 number)

[Literae]

Bonjour!

I am struggling with this formula but only for 1 Number. Please see formula below:

Formula: =HLOOKUP(A52, INDIRECT("'"&INDEX(Payroll_Number, MATCH(1, --(COUNTIF(INDIRECT("'"& Payroll_Number&"'!$b$3:$b$5"), A52)>0), 0)) &"'!$b$3:$b$5"), 3, FALSE)
Notes: A52 is the Col A with Payroll Number in. This is also B3 in the corresponding tab. B5 is the data for Col C which is B5 on the relevant tab.

I have indexed Col A and named it Payroll_Number in the Name Range section and renamed each tab to the employee's Payroll Number. Using this formula is picks up exactly what I need for every member of staff, except for Payroll "250". However if I change the Payroll Number to something else, i.e 999, it picks up the information like the rest. Its as if it will accept any number other than 250. I have even tried creating a new tab and moving the data to that tab and still get the same issue.

Summary Sheet with formula:

Payroll Number	Name	BPT	STATUS
210	Mr Smith	0	No Bradford Points
250	Mrs Smith	#N/A	#N/A
999	Mrs Smith	4	Low Points

Employee Tab with data:

Payroll No:	250
Name:	Mrs Smith
BP Score:	4
Status:	Low Points
Holiday Hours Left:	7.5

 

[jasonb75]

I can't think of any logical reason for that happening other than A52 being formatted as text, but that would cause the same error regardless of the number.

Try changing A52 in the countif part to A52+{0} and see if that works.

 

[Literae]

Just tried that and doesnt work

I just went into "Calculation Steps" and noticed this on the first page:

Not sure what the '221' means? Not sure if this has anything to do with it, but I thought maybe it was looking for Tab 221 instead of tab 250 etc.

 

[jasonb75]

That means that the countif part is trying to match to the wrong sheet. In all honesty, I wasn't exactly sure what you were trying to do with that part, the logic wasn't making a lot of sense to me. Looking at it closer, I think that you're trying to over complicate a simple task.

Unless there is something that is unclear from your example, I think that all you need is

=INDIRECT("'"&A52&"'!B5")

 

[Literae]

I am only an enthusiastic when it comes to formulas. I google what I need, find someone that has done it and adjust it to mine. I found this formula and it worked. I just didnt understand how it worked perfectly for the other 47 employees, but not this one.

I honestly have no idea what the formula I have actually does. Other than the basics; Hvlookup, countifs etc.

 

[Literae]

That means that the countif part is trying to match to the wrong sheet. In all honesty, I wasn't exactly sure what you were trying to do with that part, the logic wasn't making a lot of sense to me. Looking at it closer, I think that you're trying to over complicate a simple task.

Unless there is something that is unclear from your example, I think that all you need is

=INDIRECT("'"&A52&"'!B5")

=INDIRECT("'"&A52&"'!B5")

Worked perfectly thank you!

So is that basically searched for a tab with A52's number in and finding the corresponding B5 data?

 

[jasonb75]

I honestly have no idea what the formula I have actually does.

It's always nice to see that people, like yourself, make the effort to find something rather than just asking without making any effort as is often the case, however when you don't understand how the formula works it can lead to all kinds of chaos.

As you can see, a simpler formula is often easier to understand. With the one that I suggested, you are able to see how it is working. What you thought it was doing is 100% correct.

With the formula that you had, the countifs part is typically used to return unique matches when the data contains duplicates. Hlookup would only be needed when you don't know which column the required data is in and you need to get your result based on a heading, likewise vlookup would be used if you didn't know the row.

With your sheet, the row and column are already known so there is no need to search for the data within the sheet, we can simply go straight to it.

Advancing the formula, (assuming that there are no additional rows or columns between those in the example, and that there are no merged cells).

=INDIRECT("'"&$A52&"'!R3"+COLUMNS($B52:B52)&"C2",0)

This formula uses R1C1 notiation, which means that the columns are numbered rather than lettered. I've set it as R3C2 as a base point (Row 3, Column 2, or B3 in normal formulas) which should be the cell with the employee number in the employee tab if I'm reading it correctly.

Entering the formula into B52 will give you the employee name from B4 of the employee tab to go with the number in A52. If you drag down then the formula will adjust to A53, A54, etc.
If you drag right from B52 then the formula will still look at the number in A52, but the second part use the COLUMNS function as a counter to increase the row number in the later part so that it goes from R3C2 to R4C2 R5C2, etc (or B2, B4, B5 as you're more used to).

May not be of any use to you but it was something that I noticed in the example and may help you to learn a little about how the functions work.