Using Lookup to find the most recent event

[shane24]

My previous problem was solved by you all on this site. Thank you ver
much for that.

I have one more problem I am having a hard time figuring out.

I have created a flight log in Excel and I would like to use the looku
function to tell me when the most recent flight was in a particula
type of aircraft

Source Sheet
Column A -- Counting column
Column B -- Dates
Column C -- Aircraft Type (i.e. C172, P28R200, C150, PA28161)

Query Sheet
Column A -- Aircraft Type (same as in Source Sheet)
Column B -- Most Recent date

I am unable to come up with the most recent date. I want excel to g
through the first sheet and tell me when the most recent flight is i
the type of aircraft I specify. Can you help me come up with the righ
formula

 

[shane24]

One more thing

I tried using =VLOOKUP(A2,'Source Sheet'!A:C,2,FALSE)

A2 = the current sheet I am on that has the aircraft type that I a
searching for

The column 2 is the date column in the source sheet.

This returns me an N/

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(MAX(('Source Sheet'!C1:C1000=A2)*(B1:B1000)))

get you the maximum date. Format this target cell as Date

 

[shane24]

That returns me a #VALUE error.

I have attached a mock up file of what I am doing. Please take a loo
at it and test it out.

Thanks,
Shan

Attachment filename: log book test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46230

 

[Frank Kabel]

Hi
change the following things
1. Get rid of your merged cells - They will definitely cause trouble in
this function!
2. Adapt the formula
=SUMPRODUCT(MAX(('Source Sheet'!C1:C1000=A2)*(B1:B1000)))
as follows:
It should start in your first data row. So in your case
=SUMPRODUCT(MAX(('Source Sheet'!C3:C1000=A2)*(B3:B1000)))

Tested this with a range of non merged cells and it works o.k

 

[Frank Kabel]

Hi
change the following things
1. Get rid of your merged cells - They will definitely cause trouble in
this function!
2. Adapt the formula
=SUMPRODUCT(MAX(('Source Sheet'!C1:C1000=A2)*(B1:B1000)))
as follows:
It should start in your first data row. So in your case
=SUMPRODUCT(MAX(('Source Sheet'!C3:C1000=A2)*(B3:B1000)))

Tested this with a range of non merged cells and it works o.k

 

[Aladin Akyurek]

B2 on Query:

=MAX(IF(Source!AircraftTypeRange=A2,Source!DateRange))

which you need to confirm with control+shift+enter instead of just enter.

A2 on Query sheet houses an aircraft type.

 

[shane24]

Thank you all for your help.

I need to have the merged cells for other information in the sheet tha
you don't see in the mock up.

Is there any way to do this without unmerging the cells? It i
important that I keep them merged.

Aladin I didn't understand at all what you were trying to tell me.


Thanks,
Shan

 

[shane24]

Allright, I think I got it to work.

I used: =SUMPRODUCT(MAX(('Source Sheet'!C3:C1000=A2)*('Sourc
Sheet'!B3:B1000)))

Not having the 'Source Sheet'! command in the second half of th
equation caused an error. Now having it there it seems to work fine.

Thank you all for you help

 

[Aladin Akyurek]

=MAX(IF('Source Sheet'!C3:C1000=A2,'Source Sheet'!B3:B1000))

to be confirmed with control+shift+enter, would be a more natural formula to
use.