Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Allow VLookup to accept functions e.g. Max($B$1:$B$10)

Allow VLookup/HLookup to accept functions e.g. MAX($B$1:$B$10) as the look up value. It currently returns #REF! whether using exact or approximate value.

1 vote
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

JL shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • Kenneth Barber commented  ·   ·  Flag as inappropriate

    I think I misinterpreted the first time. If you mean something like VLOOKUP(MAX($B$1:$B$10),B1:C10,2,FALSE), then that works for me. I'm guessing that your column index was bigger than the number of columns in your table range. I sometimes forget to choose the whole table and end up only choosing the first column.

  • Kenneth Barber commented  ·   ·  Flag as inappropriate

    I think that this goes back to the idea of having a version of VLOOKUP return an array of all matches, so then you can use other functions like MAX on that array.

Feedback and Knowledge Base