VLOOKUP a value to the left of the Lookup_value

Question

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

You could do it if you added a helper column with the date at the back of the table. — CustomX, Mar 17 '15 at 12:35

score 3 · Answer 1 · edited Jul 13 '17 at 07:20

3

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))

edited Jul 13 '17 at 07:20

Stevoisiak

23,794
27
122
225

answered Mar 17 '15 at 13:41

pnuts

58,317
11
87
139

score 2 · Answer 2 · answered Mar 17 '15 at 12:34

2

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.

answered Mar 17 '15 at 12:34

Mark Fitzgerald

3,048
3
24
29

score 0 · Answer 3 · answered Mar 17 '15 at 12:39

0

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)
...

answered Mar 17 '15 at 12:39

CustomX

9,948
30
85
115

score 0 · Answer 4 · answered Dec 03 '15 at 09:19

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

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

VLOOKUP a value to the left of the Lookup_value

4 Answers4