Clever Geek Handbook

VLOOKUP value to the left of Lookup_value

Using Excel, is there a way to use a function VLOOKUP

to find the date that matches the minimum and maximum values?

enter image description here

+1


source to share


4 answers


With your layout @Mark is correct (you can't) and for the reason given. INDEX / MATCH might be a suitable alternative, but to provide some details, try to say that I30 is copied back and forth to satisfy:



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


source


No, you can watch VLOOKUP without changes, it can only look right. You should look at the INDEX / MATCH command to fix this problem.



+2


source


If you want to use a helper column to store the date, you can. In my example, I added a 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)
...
0


source


There have a simple solution for this - if you do not insist on the use of VLOOKUP()

: -)

  • Above your line 27, enter a new line, repeating the column headings ("Thomson", etc.). You can just use =I5

    , =J5

    etc.
  • Now enter this formula in I30: =DGET($H$5:$N$26;1;J27:J28)

    ... and drag it to the right.
  • For maximum values, you need to repeat the column headings again just above the "maximum" row.
0


source






More articles:


All Articles