Excel bug/feature: recursive VLOOKUP for hierarchy calculations
There's lots of Q&A websites out there talking about Excel bugs that are actually formula errors, and others that talk about recursive VLOOKUPs when they really mean iterative. To be clear on what we are talking about: Excel (2007, 2010, 2011) has an undocumented feature where you can use circular reference in the third argument of VLOOKUP without iterative calculation. Why should I care? Here's one type of problem you can solve with this "feature". Suppose we have an organizational chart and want to find how many levels deep each person is. Similar to this problem posted on MrExcel . ID BOSS NAME 1 John Smith 2 1 Susan Jones 3 2 Alice Johnson 4 2 Tim Moore 5 1 Rachel Parker 6 1 Tom Allen 7 5 Michael Gross 8 5 Kim Dole 9 6 Kathy Roberts 10 6 Betsy Foster 11 10 Mini Foster You can do it with this in cell D2: =IF(ISBLANK(B2),1,VLOOKUP(B2,A:D,4,FALSE)+1) So what's going on here? For the first row, we have a cop-out, this r...