Array Formula to find an offset value based on a certain condition.

Hi,
Let me cite after http://www.excelforum.com/forum-rule...rum-rules.html
Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Try this...

Data Range

	A	B	C	D	E	F
1	Vechicle	Miles	Date	------	Vechicle	Miles
2	4	15474	3/1/2014		3	10895
3	3	10895	3/4/2015
4	1	17761	3/1/2014
5	3	19445	6/8/2014
6	3	18298	3/1/2015
7	3	11609	6/7/2014
8	4	13725	2/15/2014
9	1	14215	7/8/2014
10	2	13935	9/12/2014
11	4	15362	8/30/2014
12	1	13101	6/3/2014
13	3	11764	7/4/2014
14	4	10133	1/8/2011
15	4	12653	6/1/2014
16	3	18324	3/2/2014
17	4	15152	2/8/2014
18	2	14242	3/7/2014
19	2	17013	3/4/2014
20	4	14573	6/1/2014

This array formula** entered in F2:

=INDEX(B2:B20,MATCH(MAX(IF(A2:A20=E2,C2:C20)),IF(A2:A20=E2,C2:C20),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

And

Also is it possible if they enter a vehicle number and a date that a different formula finds the miles for the most recent date to the date entered

Enter the date in D2 and again array formula:

Please Login or Register  to view this content.

would do.

PS. nobody wants you to publish company data. Exactly* such sample file as you attached was what I ment.

*) Well, this case text explanation was (probably) good enough, but often it is wise to show also "manualy calculated" result(s). So in such case filled with data E2, D2, and manualy assessed F2 (miles for last), G2 (miles for last before D2).

You're welcome. We appreciate the feedback!