CODEWITHSUNDEEP
excelvlookup
Egodym
Egodym

Reputation: 451

Error using Vlookup function in Excel

I have the following data set:

enter image description here

I calculate the maximum return using =max(B2:B13) in cell D3 (i.e., 13.55%), and then I would like to find the date where this maximum return was realized. Thus, I use =vlookup(D3;A2:B13;1;FALSE), and I get as a result #N/A, which is clearly wrong. How can I make this work?

Upvotes: 1

Views: 174

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

You can't use a VLOOKUP to find a value that isn't in the first column of your data table. Use an INDEX/MATCH formula instead:

=INDEX(A2:A13,MATCH(MAX(B2:B13),B2:B13,0))

A VLOOKUP is a Vertical Lookup - and is used to look up a value in the first column of a table and return a corresponding value from another column in that table (just like looking at a timetable for example).

You are trying to find the max value from column B, in column A - where it doesn't exist so you're not going to find it. In order to use a VLOOKUP the lookup_value must be in the first column of the data table.

If you want to look up a value in another column of the data table, then you need to use =INDEX(MATCH()) instead.

Upvotes: 3

Related Questions