Excel VLOOKUP is one of the most ubiquitous spreadsheet functions. Seemingly, an equivalent method to a reverse VLOOKUP will be required in some cases, because a standard VLOOKUP is unable to perform a reverse lookup. This article and video will explore a couple of alternative methods to a reverse VLOOKUP.

Standard VLOOKUP – Practical restrictions

Currently Excel VLOOKUP is unable to perform a VLOOKUP from right to left (reverse), which is a source of frustration for financial modellers, because a financial model might need to refer back (right to left) to an earlier period of data.

Certainly the data column (to the left of the formula) could be copied across to the right, but financial modellers rightfully demand a more seamless, best practice solution as an alternative to a reverse VLOOKUP.

Alternative solution to a Reverse VLOOKUP

In the below video, which employs the following financial model; financial modellers can overcome the limitations of Excel VLOOKUP to produce an alternative reverse VLOOKUP.

Strategize Financial Modelling – Reverse VLOOKUP.xls

In most cases either of these approaches will work.

❶ LOOKUP and SEARCH

=LOOKUP(1E+100,SEARCH($C$10:$C$30,J11),$B$10:$B$30)

The lookup_value of 1E+100 is a fixed input. The lookup_vector applies the SEARCH function, which references the column containing the text or lookup columns. Whilst the result_vector references the column of the resulting data.

❷ INDEX and MATCH

=INDEX($B$10:$B$30,MATCH(J18,$C$10:$C$30,0))

The array is the column containing the output value. The row_num employs the MATCH function, which references the source text or values.

Potential pitfalls with reverse VLOOKUP alternatives

In the first approach (LOOKUP and SEARCH), if the data array contains blank cells, then this reverse VLOOKUP solution will not work. The second worksheet in the video with the financial statement proved this deficiency. Hence, the INDEX and MATCH method for a reverse VLOOKUP is the only solution.

Remember when using any of Excel’s Lookup & Reference function, it is vital to maintain a uniform range or array referencing in the formula. For example, if one array references A1:A10 and the second references B2:B11, then the function will produce an erroneous solution.