Question on Hlookup

[Goddard]

What is the meaning for below formula ?
What is the function for this ROW(A5)-3?

=IF(HLOOKUP("E0016 Fully Approved",$A$4:$M$50,ROW(A5)-3,FALSE)=HLOOKUP("Grand Total",$A$4:$M$50,ROW(A5)-3,FALSE),100%,(HLOOKUP("E0016 Fully Approved",$A$4:$M$50,ROW(A5)-3,FALSE)/HLOOKUP("Grand Total",$A$4:$M$50,ROW(A5)-3,FALSE)))

I only can understand the Hlookup is horizontal look up
Below is the raw data. The whole formula is being used in column name % complete

Really need some help here. Thank you .
[TABLE="width: 1659"]
<colgroup><col><col><col><col span="7"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Count of AgrRequest[/TD]
[TD][/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lookup[/TD]
[TD]SOrg.[/TD]
[TD]ActPLTyp[/TD]
[TD]E0001 Draft[/TD]
[TD]E0007 Rej. by CBM[/TD]
[TD]E0012 Internally Approved[/TD]
[TD]E0013 Externally Submitted[/TD]
[TD]E0014 Externally Approved[/TD]
[TD]E0015 Externally Rejected[/TD]
[TD]E0016 Fully Approved[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD]% Complete[/TD]
[/TR]
[TR]
[TD]2340_AT[/TD]
[TD]2340[/TD]
[TD]AT[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]104[/TD]
[TD][/TD]
[TD]96.2%[/TD]
[/TR]
[TR]
[TD]2340_DE[/TD]
[TD]2340[/TD]
[TD]DE[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]38[/TD]
[TD][/TD]
[TD]35[/TD]
[TD]2,941[/TD]
[TD]3,040[/TD]
[TD][/TD]
[TD]96.7%[/TD]
[/TR]
[TR]
[TD]2377_SE[/TD]
[TD]2377[/TD]
[TD]SE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]74[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]98.7%[/TD]
[/TR]
[TR]
[TD]2377_NO[/TD]
[TD]2377[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]73[/TD]
[TD]73[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2377_IS[/TD]
[TD]2377[/TD]
[TD]IS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2377_FI[/TD]
[TD]2377[/TD]
[TD]FI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2377_DK[/TD]
[TD]2377[/TD]
[TD]DK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]279[/TD]
[TD]279[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2647_AT[/TD]
[TD]2647[/TD]
[TD]AT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2647_CH[/TD]
[TD]2647[/TD]
[TD]CH[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]184[/TD]
[TD]190[/TD]
[TD][/TD]
[TD]96.8%[/TD]
[/TR]
[TR]
[TD]2786_BE[/TD]
[TD]2786[/TD]
[TD]BE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]452[/TD]
[TD]452[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2619_RB[/TD]
[TD]2619[/TD]
[TD]RB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]418[/TD]
[TD]419[/TD]
[TD][/TD]
[TD]99.8%[/TD]
[/TR]
[TR]
[TD]2376_NL[/TD]
[TD]2376[/TD]
[TD]NL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]397[/TD]
[TD]399[/TD]
[TD][/TD]
[TD]99.5%[/TD]
[/TR]
[TR]
[TD]2799_ES[/TD]
[TD]2799[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]405[/TD]
[TD]405[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2799_IC[/TD]
[TD]2799[/TD]
[TD]IC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2795_PT[/TD]
[TD]2795[/TD]
[TD]PT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]68[/TD]
[TD]68[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2789_IT[/TD]
[TD]2789[/TD]
[TD]IT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1,019[/TD]
[TD]1,020[/TD]
[TD][/TD]
[TD]99.9%[/TD]
[/TR]
[TR]
[TD]2334_FR[/TD]
[TD]2334[/TD]
[TD]FR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1,776[/TD]
[TD]1,776[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2622_SK[/TD]
[TD]2622[/TD]
[TD]SK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]187[/TD]
[TD]187[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2615_CZ[/TD]
[TD]2615[/TD]
[TD]CZ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]430[/TD]
[TD]430[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2616_PL[/TD]
[TD]2616[/TD]
[TD]PL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]814[/TD]
[TD]814[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2677_CE[/TD]
[TD]2677[/TD]
[TD]CE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]568[/TD]
[TD]568[/TD]
[TD][/TD]
[TD]100.0%[/TD]
[/TR]
[TR]
[TD]2626_HU[/TD]
[TD]2626[/TD]
[TD]HU[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]128[/TD]
[TD][/TD]
[TD][/TD]
[TD]123[/TD]
[TD]253[/TD]
[TD][/TD]
[TD]48.6%[/TD]
[/TR]
[TR]
[TD]Grand Total_[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD]32[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]169[/TD]
[TD]1[/TD]
[TD]40[/TD]
[TD]10,340[/TD]
[TD]10,584[/TD]
[TD][/TD]
[TD]97.7%[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1659"]
<colgroup><col><col><col><col span="7"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the forum.

With all due respect to whoever wrote that formula, it's pretty awful. That whole formula can be replaced with:

=L5/M5

or a little fancier:

=IFERROR(L5/M5,"Completion percent not available")

All the HLOOKUP does is find the column with the right heading, then the ROW(A5)-3 term tells it to go down the same number of rows to the row the formula is in. That is, if the formula is in row 5, then ROW(A5)-3 = 2, and when you look at the second row down in the range $A$4:$M$50, you get row 5.

So HLOOKUP is used 4 times, to find a particular column, then the ROW(A5)-3 is used to find a particular row. But we already know the row and column we need!

Hope this helps! Let me know if you have further questions.