This is a follow-up to a similar article on reverse VLOOKUP options. There will be instances where a reverse HLOOKUP, like a VLOOKUP, is required in a financial model. The following feature will outline two alternate approaches, which enable a form of reverse HLOOKUP.
Conventional HLOOKUP – Current restrictions
The Excel HLOOKUP function, similar to VLOOKUP, is incapable of looking up values backwards – from bottom to top (reverse). Understandably this can pose a problem in certain financial models, such as referencing the previous day’s close of financial data, or earlier presented financial KPIs in a summary table – as presented in the below video and financial model.
Alternative approaches to a Reverse HLOOKUP
This video and financial model illustrate two alternative approaches, which will successfully overcome the current restrictions an HLOOKUP has with reverse lookups. These two options, in terms of Excel functions employed; are the same approaches outlined in the reverse VLOOKUP article.
Strategize Financial Modelling – Reverse HLOOKUP.xls
❶LOOKUP and SEARCH
=LOOKUP(1E+100,SEARCH($C$9:$W$9,B17),$C$8:$W$8)
The lookup_value of 1E+100 is a fixed input. The lookup_vector adopts the SEARCH function, which references the column comprising the text or lookup columns. Whereas the result_vector references the column of the ensuing data.
❷ INDEX and MATCH
=INDEX($C$8:$W$8,MATCH(B25,$C$9:$W$9,0))
The array is the column holding the output value. The row_num uses the MATCH function, which stores the referenced text or values and then looks up the necessary data array.
Fundamental considerations with reverse HLOOKUP alternatives
Similar to the caveat in the reverse VLOOKUP article, the first option (LOOKUP and SEARCH) will deliver erroneous results, if the data array contains some blank cells. Thus in this instance the second approach, the INDEX and MATCH for a reverse HLOOKUP is the only option.
As the video proved, it is paramount to sustain uniform range or array referencing in the formula; otherwise erroneous answers will be derived .