Lookup text in one column, return date from same row in different column

dallen8028

New Member
Joined
Jan 28, 2013
Messages
30
Using Excel 2016.

In the GMH Table, I am attempting to Lookup "Gas Tank Fill" in Event column of the History Table to return the date from same row in the Out column of the History Table using a table that will advance the formula when the next row is added.

=LOOKUP("Gas Tank Fill",Table6185115[Event],Table6185115[Out])

This formula works but is limited to just one match. As you can see there are two such matches with different dates. How do I get it to place the different dates in the next row that match the same "text"? Please see tables below. Thank you in advance.
History
TimesGeneralMileageExpenses
OutInTotal Time UsedDriverEventNotesStart Miles
Kilometers
Stop Miles
Kilometers
Total Miles
Kilometers
GallonsCost per GallonCost per FillService CostsExchangeUSD
8/7/18 15:588/9/18 17:001:02Sampuel, JoseGas Tank Fill103,600103,805.0205.035.00 Q 26.20 Q 917.00 Q 7.48 $ 122.55
8/8/18 13:358/8/18 16:002:25Adams, LawrenceOil & Filter Change103,805103,814.09.0 Q 26.20 Q - Q 523.00 Q 7.48 $ 69.92
8/14/18 12:308/14/19 16:003:30Yantuche, AngelMaintenanceReplace 2 Back Tires103,814103,835.021.0 Q 26.20 Q - Q 1,899.00 Q 7.48 $ 253.86
8/30/18 9:008/30/19 11:302:30Sampuel, JoseDelivery Service103,835103,848.013.0 Q 26.20 Q - Q 7.55 $ -
2/27/19 0:002/27/19 0:000:00Calca, FloriInsurance103,848103,848.0 Q 26.20 Q - Q 3,330.91 Q 7.71 $ 432.00
8/31/18 11:008/31/18 12:001:00Sampuel, JoseGas Tank Fill103,848103,852.04.022.82 Q 27.39 Q 624.99
2/27/19 0:008/30/19 11:3010:27103,600103,852.0252.057.82 Q 26.40 Q 1,541.99 Q 5,752.91 $ 878.33
Gas Mileage History
Date Tank FilledMiles
Kilometers
MPG
KGP
8/7/2018103600
8/7/2018103852
8/7/2018
8/7/2018
8/7/2018252

<tbody>
</tbody>
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
Re: Lookup text in one culomn, return date from same row in different column

Is this what you want (not sure how you would do this with tables)?

in Q5
=IFERROR(INDEX($A$5:$A$1000,AGGREGATE(15,6,ROW($A$5:$A$1000)/(($E$5:$E$1000="Gas Tank Fill")),ROWS(A$5:A5))-(5-1),1),"")
and copy down the column for as many rows as you expect in column A

format column Q as per column A

If first data row changes (row 5) to another row change the red 5s in the formula above.
 
Last edited:

dallen8028

New Member
Joined
Jan 28, 2013
Messages
30
Re: Lookup text in one culomn, return date from same row in different column

Thank you for your response Special-K99. The last image I pasted didn't do it justice. I hope the this one helps you to more understand where the cells are.
 
Last edited by a moderator:

dallen8028

New Member
Joined
Jan 28, 2013
Messages
30
Re: Lookup text in one culomn, return date from same row in different column

Thank you for the information. Could you please delete my last entry, not the post, but the last entry? Thank you in advance.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
Re: Lookup text in one culomn, return date from same row in different column

I cant do that only mods or possibly yourself - looks like its gone though.
 

dallen8028

New Member
Joined
Jan 28, 2013
Messages
30
Re: Lookup text in one culomn, return date from same row in different column

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html
Thank you for the link(s). I am saddened to report that none of the files appear to work after downloading. I am using Excel 2016. The Excel jeanie links are no longer available and after downloading and and opening the other excel files, there are no noticeable differences to Excel. The Border Copy and Pasted didn't do anything either. Please advise. Thank you again in advance.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
Re: Lookup text in one culomn, return date from same row in different column

If something's not working on this forum you need to contact a moderator.
 

dallen8028

New Member
Joined
Jan 28, 2013
Messages
30
Re: Lookup text in one culomn, return date from same row in different column

thank you
 

Forum statistics

Threads
1,082,360
Messages
5,364,920
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top