Dear experts,

In sheet "Team", cell N3 is a lookup value. In sheet "Group", array table is
E9:L42 and row index is 42. So, in sheet "Team" cell N4, I set a formula to
lookup value in sheet "Group" as follows:

=HLOOKUP(N3,'Team'!E9:L42,42,False)

However, the result of a return is #Ref!. Please advise what's the problem
and how to fix it.

Thanks in advance.

Hi,
Your row index (42) is not correct, because row index count is from row 9
(starting point, so this will be row index 1) to row 42 (your end point, so
this will be row index 34). So basically the correct row index should be 34.

=HLOOKUP(N3,'Team'!E9:L42,34,False)




"Freshman" wrote:

Dear experts,

In sheet "Team", cell N3 is a lookup value. In sheet "Group", array table is
E9:L42 and row index is 42. So, in sheet "Team" cell N4, I set a formula to
lookup value in sheet "Group" as follows:

=HLOOKUP(N3,'Team'!E9:L42,42,False)

However, the result of a return is #Ref!. Please advise what's the problem
and how to fix it.

Thanks in advance.

Thanks, Lorderon. How stupid I am of overlooking the number of rows. Thanks
once again. Regards.

"Lorderon" wrote:

Hi,
Your row index (42) is not correct, because row index count is from row 9
(starting point, so this will be row index 1) to row 42 (your end point, so
this will be row index 34). So basically the correct row index should be 34.

=HLOOKUP(N3,'Team'!E9:L42,34,False)




"Freshman" wrote:

Dear experts,

In sheet "Team", cell N3 is a lookup value. In sheet "Group", array table is
E9:L42 and row index is 42. So, in sheet "Team" cell N4, I set a formula to
lookup value in sheet "Group" as follows:

=HLOOKUP(N3,'Team'!E9:L42,42,False)

However, the result of a return is #Ref!. Please advise what's the problem
and how to fix it.

Thanks in advance.