Using Lookup to find the most recent event

  • Thread starter Thread starter shane24
  • Start date Start date
S

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
 
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/
 
Hi
try
=SUMPRODUCT(MAX(('Source Sheet'!C1:C1000=A2)*(B1:B1000)))

get you the maximum date. Format this target cell as Date
 
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
 
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
 
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.
 
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
 
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
 
=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.
 
Back
Top