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 .

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

<colgroup><col><col><col><col span="7"><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col span="7"><col><col><col></colgroup><tbody>
</tbody>

 

[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.