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

VEHICLE	Open Reading	Closing Reading		Vehicle	Open	Close
V1	1000	1050		V1	1000
V2	500	550		V2	500
V3	5000	5100		V3	5000
V4	7000	7100		V4	7000
V1	1050	2000
V2	550	600
V3	5100	5200
V4	7100	8000

<tbody>
</tbody>

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​	VEHICLE	Open Reading	Closing Reading		Vehicle	Open	Close
2​	V1​	1000​	1050​		V1	1000​	2000​
3​	V2​	500​	550​		V2	500​	600​
4​	V3​	5000​	5100​		V3	5000​	5200​
5​	V4​	7000​	7100​		V4	7000​	8000​
6​	V1​	1050​	2000​
7​	V2​	550​	600​
8​	V3​	5100​	5200​
9​	V4​	7100​	8000​

<tbody>
</tbody>

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