SOLVED

hlookup help - structured reference

%3CLINGO-SUB%20id%3D%22lingo-sub-1504814%22%20slang%3D%22en-US%22%3Ehlookup%20help%20-%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504814%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20am%20working%20on%20a%20problem%20where%20I%20am%20stumped%20with%20hlookup...%20the%20prompt%20asks%20me%20to%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20E2%2C%20enter%20a%20formula%20using%20the%20HLOOKUP%20function%20as%20follows%20to%20determine%20a%20student's%20potential%20base%20hourly%20rate%20(which%20is%20based%20on%20the%20number%20of%20years%20of%20post-secondary%20education)%3A%3CBR%20%2F%3Ea.%20Use%20a%20structured%20reference%20to%20look%20up%20the%20value%20in%20the%20Post-Secondary%20Years%20column.%20Retrieve%20the%20value%20in%20the%202nd%20row%20of%20the%20table%20in%20the%20range%20P13%3AU14%2C%20using%20an%20absolute%20reference.%20Because%20base%20hourly%20rate%20is%20tiered%20based%20on%20the%20number%20of%20years%20of%20education%2C%20find%20an%20approximate%20match.%3CBR%20%2F%3Eb.%20Fill%20the%20formula%20into%20the%20range%20E3%3AE31%2C%20if%20necessary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20when%20I%20complete%20the%20formula%20the%20result%20I%20get%20is%20%23SPILL!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ereally%20need%20some%20help%20on%20this%20issue!%20thx%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1504814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

Hi there,

 

So I am working on a problem where I am stumped with hlookup... the prompt asks me to:

 

In cell E2, enter a formula using the HLOOKUP function as follows to determine a student's potential base hourly rate (which is based on the number of years of post-secondary education):
a. Use a structured reference to look up the value in the Post-Secondary Years column. Retrieve the value in the 2nd row of the table in the range P13:U14, using an absolute reference. Because base hourly rate is tiered based on the number of years of education, find an approximate match.
b. Fill the formula into the range E3:E31, if necessary.

 

but when I complete the formula the result I get is #SPILL!

 

really need some help on this issue! thx

3 Replies
Highlighted
Best Response confirmed by gltech (Occasional Contributor)
Solution

@gltech 

1) In the formula the lookup value is entire column. Formula returns spill (an array) for which there is no place in one cell of the table, thus an error. Change on the value from current row reference.

2) Lookup range has only two columns, and you try to return the result from row 14. Thus #REF error, since there are no so many rows in the range. Change on 2.

 

Finally

=HLOOKUP([@[Post-Secondary Years]],$P$13:$U$14,2,TRUE)
Highlighted
yup, thank you so much
Highlighted

@gltech , you are welcome