List Function with Criteria - Index Match with Criteria

Hi All,

Have attached a slimmed down version of a spreadsheet I use to log data. Looking for a formula that will auto capture flight numbers from the Trip sheet and populate the TLH and N45DJ sheets for those flights that having the matching criteria.

Criteria for the TLH sheet is cell TLH!A1 matching with TripsL:L.

Criteria for the N45DJ sheet is cell N45DJ! matching with Trips!B:B criteria.

I have been trying various formula's and the best I can get is an Index Match but, having a very difficult time using the formula with a criteria.

Thank you in advance.

You could use two helper columns in the Trips sheet to help identify the appropriate records. For example, you can use this formula in cell AM3:

=IF(L3=TLH!$A$1,MAX(AM$2:AM2)+1,"-")

and this one in AN3:

=IF(B3=N45DJ!$A$1,MAX(AN$2:AN2)+1,"-")

Copy these down to the bottom of your data (or beyond, to accommodate new data being added), and you will get sequential numbers down each column which identify matching records.

Then in the other two sheets you can use a formula like this in A6 of the TLH sheet:

=IFERROR(INDEX(Trips!A:A,MATCH(ROWS($1:1),Trips!$AM:$AM,0)),"")

and this one in A3 of the N45DJ sheet:

=IFERROR(INDEX(Trips!A:A,MATCH(ROWS($1:1),Trips!$AN:$AN,0)),"")

These can be copied across and down to fill your tables.

Hope this helps.

Pete

Hi Pete,

That is a great idea. And, it does meet the needs I presented.

What I didn't post is that I add and subtract pilots and aircraft regularly. While not impossible, I would need to added a separate hidden column for each various aircraft and pilot. Was hoping for a solution that can easily expand and contract with the dynamics of the logs.

Thank you for the post. Any recommendations/suggestions.