The reason you're getting the same value is that the formula mentioned =HLOOKUP($F$2,$B$2:$E$6,2) only works for the second row.
By hard coding the row offset of 2, you'll just get the value from the second row of the table all the way down the column.
If you move the table to row 1, you could easily change this to
=HLOOKUP($F$2,$B$2:$E$6,ROW()) There are many other ways of doing this but I'd suggest this is perhaps the easiest & most consistent with the original formula.
Financial Modeling | Management Reporting | Spreadsheet Auditing | Training
Keep Up with TechRepublic