We all know that HLOOKUP is an incredibly powerful, time-saving function in Excel. But it can betray us if we don’t watch out.
Problem: You’re working in your model and need to enter or delete a row in your cash flow tab for the addition or deletion of a line item, or the addition or deletion of a spacer row. Because you are rushing to get the work done, you forget that the HLOOKUP is a fragile function in that the offset variable (what Excel unhelpfully calls the “row_index_number”) needs to be updated if the table array (specified grid coordinate range) is impacted by the insertion or deletion of rows.
As a result, you fail to manually update the row offset variable, and (unbeknownst to you) your HLOOKUP function that is supposed to pull back a value from the NOI line ends up pulling back a value from the wrong row. You don’t realize this because the value it pulls back is similar in size to the value that it should have pulled back from the now-shifted NOI row. So you go on with your merry business, blind to the fact that your model is now doing evil things right in front of you.
REFM 60-Second Skills Solution: Instead of hard-keying in the row index number, use the ROWS function to make the counting of the rows dynamic and flexible.
The HLOOKUP syntax is: =HLOOKUP(lookup_value, table_array,row_index_number)
The ROWS syntax is: =ROWS(array), so all you have to do in your HLOOKUP function is replace the row_index_number element with: ROWS(table_array)
Once you’ve done that, you can insert and delete rows within the table array to your heart’s content and not have to ever remember to update the row_index_number.
Enjoy!
