CODEWITHSUNDEEP
excelexcel-formulavlookupworksheet-function
Hamish Mckie
Hamish Mckie

Reputation: 17

VLOOKUP a value to the left of the Lookup_value

Using Excel, is there any way to use the VLOOKUP function to find the date corresponding the minimum and maximum values?

enter image description here

Upvotes: 1

Views: 589

Answers (4)

pnuts
pnuts

Reputation: 59485

With your layout @Mark is correct (you can't) and for the reason stated. INDEX/MATCH may be a suitable alternative but to provide some detail, please try in say I30 copied across and down to suit: 

 =INDEX($H$6:$H$26,MATCH(I27,I$6:I$26,0))

Upvotes: 3

Thomas Landauer
Thomas Landauer

Reputation: 8355

There is an easy solution for this - if you don't insist on using VLOOKUP() :-)

  1. Above your row 27 enter a new row, repeating the column headings ("Thomson" etc.). You can just use =I5, =J5 and so on.
  2. Now enter this formula in I30: =DGET($H$5:$N$26;1;J27:J28) ... and drag it to the right.
  3. For the maximum values you need to repeat the column headings just above the "maximum" row again.

Upvotes: 0

CustomX
CustomX

Reputation: 10113

If you're willing to use a helper column to hold the date, you can. In my example I added the helper column in column N. So column N also displays the date from column H.

=VLOOKUP(I27;$I$6:$O$26;7;0)
=VLOOKUP(I28;$I$6:$O$26;7;0)
...

Upvotes: 0

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

No, there is no way to look left using VLOOKUP - it can only look right. You should look at INDEX/MATCH combo to solve this.

Upvotes: 2

Related Questions