Vlookup max value

[Steven Cohen]

I am having difficulty trying to get the max closing reading from this table.

I can get the min value using =MIN(VLOOKUP(E2,A2:B10,2,FALSE)) it brings up the 1st entry no problem. But I tried the same formula with =MAX(VLOOKUP,E2,A2:B10,3,FALSE)) but it brings up the closing reading for the 1st entry in cell C2 instead of C6

Any suggestions to get the value in C6?

Thanks

[TABLE="width: 500"]
<tbody>[TR]
[TD]VEHICLE[/TD]
[TD]Open Reading[/TD]
[TD]Closing Reading[/TD]
[TD][/TD]
[TD]Vehicle[/TD]
[TD]Open[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]V1
[/TD]
[TD]1000[/TD]
[TD]1050[/TD]
[TD][/TD]
[TD]V1[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V2[/TD]
[TD]500[/TD]
[TD]550[/TD]
[TD][/TD]
[TD]V2[/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V3[/TD]
[TD]5000[/TD]
[TD]5100[/TD]
[TD][/TD]
[TD]V3[/TD]
[TD]5000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V4[/TD]
[TD]7000[/TD]
[TD]7100[/TD]
[TD][/TD]
[TD]V4[/TD]
[TD]7000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V1[/TD]
[TD]1050[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V2[/TD]
[TD]550[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V3[/TD]
[TD]5100[/TD]
[TD]5200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V4[/TD]
[TD]7100[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]VEHICLE[/TD]
[TD]Open Reading[/TD]
[TD]Closing Reading[/TD]
[TD][/TD]
[TD]Vehicle[/TD]
[TD]Open[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

V1​

[/TD]
[TD]

1000​

[/TD]
[TD]

1050​

[/TD]
[TD][/TD]
[TD]V1[/TD]
[TD]

1000​

[/TD]
[TD]

2000​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

V2​

[/TD]
[TD]

500​

[/TD]
[TD]

550​

[/TD]
[TD][/TD]
[TD]V2[/TD]
[TD]

500​

[/TD]
[TD]

600​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

V3​

[/TD]
[TD]

5000​

[/TD]
[TD]

5100​

[/TD]
[TD][/TD]
[TD]V3[/TD]
[TD]

5000​

[/TD]
[TD]

5200​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

V4​

[/TD]
[TD]

7000​

[/TD]
[TD]

7100​

[/TD]
[TD][/TD]
[TD]V4[/TD]
[TD]

7000​

[/TD]
[TD]

8000​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

V1​

[/TD]
[TD]

1050​

[/TD]
[TD]

2000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

V2​

[/TD]
[TD]

550​

[/TD]
[TD]

600​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

V3​

[/TD]
[TD]

5100​

[/TD]
[TD]

5200​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

V4​

[/TD]
[TD]

7100​

[/TD]
[TD]

8000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


g2=MAX(IF($A$2:$A$9=E2,$C$2:$C$9)) control +shift +enter copy down

 

[Steven Cohen]

Hi MARZIOTULLIO

When I enter the formula, it returns the MAX value in Column C = 8000

 

[MARZIOTULLIO]

Hi

After you type in the formula, don't just press enter.
Instead, press CTRL + SHIFT + ENTER


If you've already entered the formula, then highlight the cell with the formula and press F2.
Then press CTRL + SHIFT + ENTER




When entered correctly, the formula will be enclosed in {brackets}

 

[Steven Cohen]

OH MAN!!!! If I could reach through the screen and shake your hand! I have been struggling too long with this.

It works like a charm!

 

[MARZIOTULLIO]

Hi Steven
You are welcome

try also

g2=MAX(INDEX(($A$2:$A$9=E2)*$C$2:$C$9,0)) just press enter copy down