VLOOKUP - returning value with last entry in a table

In your formula, change the 0 to 1

Please Login or Register  to view this content.

Tom, that doesnt work Add another 1/1 at the end, it does not get picked up.

Niel if you are OK with a helper column (which you can hide), try this...

	A	B	C	D	E	F
1	1-Jan	6	42005.01	1-Jan
2	2-Jan	7	42006.01	9		<-- I'd like the formula to return 9 instead of 6
3	3-Jan	8	42007.01	44
4	1-Jan	9	42005.02
5	2-Jan	10	42006.02
6	3-Jan	11	42007.02
7	1-Jan	1	42005.03
8	2-Jan	2	42006.03
9	3-Jan	3	42007.03
10	1-Jan	44	42005.04
11	2-Jan	55	42006.04
12	3-Jan	6	42007.04

Column C is the helper with this, copied down...
=A1+COUNTIF($A$1:A1,A1)/100

Then to pull out your value, use this ARRAY formula...
D3=INDEX($B$1:$B$12,MATCH(MAX(IF(INT($C$1:$C$12)=$D$1,$C$1:$C$12,0)),$C$1:$C$12,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Originally Posted by gmr4evr1

In your formula, change the 0 to 1
=VLOOKUP(D1,A:B,2,1)

If you get the correct result with that formula it's just dumb luck!

Look at this data:

Data Range

	A	B	C	D
1	1-Jan	10		1-Jan
2	2-Jan	7		10
3	1-Jan	8
4	1-Jan	2
5	1-Jan	10
6	3-Jan	11
7	1-Jan	1
8	------	------	------	------

The formula returns an incorrect result.

Use this formula:

=LOOKUP(2,1/(A1:A7=D1),B1:B7)

Wow, I guess it was dumb luck. When I tested it before posting, it worked perfectly. But I only tested it with 3-Jan. Had I tested it with the other dates as well, I would have seen that it did not work. Thanks for having my back guys. My apologies to OP.

You're welcome!