hlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-2310746%22%20slang%3D%22en-US%22%3Ehlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310746%22%20slang%3D%22en-US%22%3E%3CP%3EUse%20a%20structured%20reference%20to%20look%20up%20the%20value%20in%20the%20post%20secondary%20years%20column.%20Retrieve%20the%20value%20in%20the%20second%20row%20of%20the%20table%20in%20the%20range%20P13%3AU14%20using%20absolute%20reference%20.%20Because%20base%20hourly%20rate%20is%20tiered%20based%20on%20the%20number%20of%20years%20of%20education%2C%20find%20approximate%20match.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2310746%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310823%22%20slang%3D%22en-US%22%3ERe%3A%20hlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310823%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3EIt's%20not%20a%20good%20idea%20to%20do%20an%20horizontal%20lookup%20for%20a%20vertical%20table.%3CBR%20%2F%3EI%20don't%20know%20what%20exactly%20you%20pretend%20with%20the%20lookup%2C%20but%20I%20guess%20you%20want%20to%20count.%20I%20did%20it%20with%20a%20VLOOKUP%20and%20a%20COUNTIF.%3CBR%20%2F%3EKind%20regards%3CBR%20%2F%3EHans%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Use a structured reference to look up the value in the post secondary years column. Retrieve the value in the second row of the table in the range P13:U14 using absolute reference . Because base hourly rate is tiered based on the number of years of education, find approximate match.

3 Replies
Giving me a spill error

Hi,
It's not a good idea to do an horizontal lookup for a vertical table.
I don't know what exactly you pretend with the lookup, but I guess you want to count. I did it with a VLOOKUP and a COUNTIF.
Kind regards
Hans

I'm guessing you are trying this formula (below) that is giving the spill error? With office 365 and switching to dynamic arrays being the default method of evaluating formulas, you can no longer use entire column references within structured tables.

=HLOOKUP([Post-Secondary Years],$Q$13:$U$14,2,TRUE)

Instead, you need to use the implicit intersection operator (@ symbol). When you're constructing the formula in cell E2, click on cell F2 and excel will add it for you.

=HLOOKUP([@[Post-Secondary Years]],$Q$13:$U$14,2,TRUE)