Hlookup

[robvasi]

IFERROR(HLOOKUP(D22,D6:H19,14,TRUE),"Value Not Found")

With the 'range lookup' set to 'TRUE', I expected the HLOOKUP formula to find the closest value to the 'lookup value' and return a value according to the 'row index' which is the year.

Please see the attached spreadsheet.

Example 1. The 'lookup value' is $400,000 and it does not find a value closest to $400,00.00 even though the 'range lookup' is set to 'TRUE' which is designated as an approximate value.

Example 2. As expected, the formula renders the correct year if a value of $500,000 is entered if $500,000 is in row one.

Example 3. The value of $477,637.16 is entered in row 1 and it can't find the year, even though it is an exact match.

What I want to happen is that the closest match to the entered dollar amount is looked for in row one and the year that dollar amount is achieved, or the closest match, is shown as the year.


HLOOKUP.xlsx

[Jonmo1]

With TRUE as the 4th argument in Hlookup,
The data in the top row of the range MUST be sorted in Ascending order from Left to Right.
But this is not the case in that book.

[ConneXionLost]

Two problems:

- When using TRUE for the range lookup, the values on row 6 would need to be sorted ascending; and
- the lookup value would need to be larger than the smallest value in row 6.

Cheers,

[robvasi]

Thank you.

So, it seems there is no way to use HLOOKUP to do this. The values in row 6 can't be changed.

[ChemistB]

There's always a way. In J5 copied down (you could do this in a separate tab)

=SMALL($D$6:$H$6,ROWS($A$1:$A1))

In D23 copied right

=IF(D22<$J$5, HLOOKUP($J$5,$D$6:$H$19,14,FALSE), HLOOKUP(INDEX($J$5:$J$9, MATCH(D22, $J$5:$J$9)),$D$6:$H$19, 14, FALSE))
Does that work for you?

[robvasi]

Yes, this worked fine.

What do the $ do to the formula?

[ChemistB]

A $ in an Excel Formula is an anchor. If it's in front of a row number, that number won't change as you copy the formula into another row. If its in front of a column letter, that column letter won't change when you copy it to the left or right.
For example,
=A1 + $B$1 in cell C1
if copied to C2
=A2 + $B$1
if copied to D4
= B4 + $B$1

[robvasi]

Thank you. I continue to be impressed by Excel and by the knowledge in this forum. I also appreciated the helpful attitude.