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

[dallen8028]

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.

Sampuel, Jose	Gas Tank Fill
Adams, Lawrence
Yantuche, Angel	Maintenance	Replace 2 Back Tires
Sampuel, Jose
Calca, Flori	Insurance
Sampuel, Jose	Gas Tank Fill

<tbody>
[TD="class: xl85, colspan: 15"]History[/TD]

[TD="class: xl80, colspan: 3"]Times[/TD]
[TD="class: xl80, colspan: 3"]General[/TD]
[TD="class: xl80, colspan: 4"]Mileage[/TD]
[TD="class: xl81, colspan: 5"]Expenses[/TD]

[TD="class: xl72, width: 120"]Out[/TD]
[TD="class: xl72, width: 128"]In[/TD]
[TD="class: xl72, width: 103"]Total Time Used[/TD]
[TD="class: xl72, width: 103"]Driver[/TD]
[TD="class: xl72, width: 89"]Event[/TD]
[TD="class: xl72, width: 89"]Notes[/TD]
[TD="class: xl72, width: 227"]Start Miles
Kilometers[/TD]
[TD="class: xl72, width: 89"]Stop Miles
Kilometers[/TD]
[TD="class: xl72, width: 124"]Total Miles
Kilometers[/TD]
[TD="class: xl72, width: 124"]Gallons[/TD]
[TD="class: xl72, width: 104"]Cost per Gallon[/TD]
[TD="class: xl72, width: 98"]Cost per Fill[/TD]
[TD="class: xl72, width: 157"]Service Costs[/TD]
[TD="class: xl72, width: 103"]Exchange[/TD]
[TD="class: xl72, width: 102"]USD[/TD]

[TD="class: xl74, align: right"]8/7/18 15:58[/TD]
[TD="class: xl74, align: right"]8/9/18 17:00[/TD]
[TD="class: xl75, align: right"]1:02[/TD]

[TD="class: xl73, align: right"]103,600[/TD]
[TD="class: xl76, align: right"]103,805.0[/TD]
[TD="class: xl76, align: right"]205.0[/TD]
[TD="class: xl77, align: right"]35.00[/TD]
[TD="class: xl78"] Q 26.20[/TD]
[TD="class: xl78"] Q 917.00[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"] Q 7.48[/TD]
[TD="class: xl79"] $ 122.55[/TD]

[TD="class: xl74, align: right"]8/8/18 13:35[/TD]
[TD="class: xl74, align: right"]8/8/18 16:00[/TD]
[TD="class: xl75, align: right"]2:25[/TD]

[TD="colspan: 2"]Oil & Filter Change[/TD]
[TD="class: xl73, align: right"]103,805[/TD]
[TD="class: xl76, align: right"]103,814.0[/TD]
[TD="class: xl76, align: right"]9.0[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl78"] Q 26.20[/TD]
[TD="class: xl78"] Q -[/TD]
[TD="class: xl78"] Q 523.00[/TD]
[TD="class: xl78"] Q 7.48[/TD]
[TD="class: xl79"] $ 69.92[/TD]

[TD="class: xl74, align: right"]8/14/18 12:30[/TD]
[TD="class: xl74, align: right"]8/14/19 16:00[/TD]
[TD="class: xl75, align: right"]3:30[/TD]

[TD="class: xl73, align: right"]103,814[/TD]
[TD="class: xl76, align: right"]103,835.0[/TD]
[TD="class: xl76, align: right"]21.0[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl78"] Q 26.20[/TD]
[TD="class: xl78"] Q -[/TD]
[TD="class: xl78"] Q 1,899.00[/TD]
[TD="class: xl78"] Q 7.48[/TD]
[TD="class: xl79"] $ 253.86[/TD]

[TD="class: xl74, align: right"]8/30/18 9:00[/TD]
[TD="class: xl74, align: right"]8/30/19 11:30[/TD]
[TD="class: xl75, align: right"]2:30[/TD]

[TD="colspan: 2"]Delivery Service[/TD]
[TD="class: xl73, align: right"]103,835[/TD]
[TD="class: xl76, align: right"]103,848.0[/TD]
[TD="class: xl76, align: right"]13.0[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl78"] Q 26.20[/TD]
[TD="class: xl78"] Q -[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"] Q 7.55[/TD]
[TD="class: xl79"] $ -[/TD]

[TD="class: xl74, align: right"]2/27/19 0:00[/TD]
[TD="class: xl74, align: right"]2/27/19 0:00[/TD]
[TD="class: xl75, align: right"]0:00[/TD]

[TD="class: xl73, align: right"]103,848[/TD]
[TD="class: xl76, align: right"]103,848.0[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl77"][/TD]
[TD="class: xl78"] Q 26.20[/TD]
[TD="class: xl78"] Q -[/TD]
[TD="class: xl78"] Q 3,330.91[/TD]
[TD="class: xl78"] Q 7.71[/TD]
[TD="class: xl79"] $ 432.00[/TD]

[TD="class: xl74, align: right"]8/31/18 11:00[/TD]
[TD="class: xl74, align: right"]8/31/18 12:00[/TD]
[TD="class: xl75, align: right"]1:00[/TD]

[TD="class: xl73, align: right"]103,848[/TD]
[TD="class: xl76, align: right"]103,852.0[/TD]
[TD="class: xl76, align: right"]4.0[/TD]
[TD="class: xl77, align: right"]22.82[/TD]
[TD="class: xl78"] Q 27.39[/TD]
[TD="class: xl78"] Q 624.99[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"][/TD]
[TD="class: xl79"][/TD]

[TD="class: xl74, align: right"]2/27/19 0:00[/TD]
[TD="class: xl74, align: right"]8/30/19 11:30[/TD]
[TD="class: xl75, align: right"]10:27[/TD]

[TD="class: xl73, align: right"]103,600[/TD]
[TD="class: xl76, align: right"]103,852.0[/TD]
[TD="class: xl76, align: right"]252.0[/TD]
[TD="class: xl77, align: right"]57.82[/TD]
[TD="class: xl78"] Q 26.40[/TD]
[TD="class: xl78"] Q 1,541.99[/TD]
[TD="class: xl78"] Q 5,752.91[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl88"] $ 878.33[/TD]

[TD="class: xl82, colspan: 3"]Gas Mileage History[/TD]
[TD="class: xl89"][/TD]
[TD="class: xl89"][/TD]

[TD="class: xl67, width: 120"]Date Tank Filled[/TD]
[TD="class: xl67, width: 128"]Miles
Kilometers[/TD]
[TD="class: xl67, width: 103"]MPG
KGP[/TD]

[TD="class: xl65, align: right"]8/7/2018[/TD]
[TD="class: xl66, align: right"]103600[/TD]
[TD="class: xl70"][/TD]

[TD="class: xl69, align: right"]8/7/2018[/TD]
[TD="class: xl68, align: right"]103852[/TD]
[TD="class: xl71"][/TD]

[TD="class: xl69, align: right"]8/7/2018[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl71"][/TD]

[TD="class: xl69, align: right"]8/7/2018[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl71"][/TD]

[TD="class: xl65, align: right"]8/7/2018[/TD]
[TD="class: xl66, align: right"]252[/TD]
[TD="class: xl70"][/TD]

</tbody>

 

[Special-K99]

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.

 

[dallen8028]

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.

 

[Special-K99]

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

 

[dallen8028]

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]

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]

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]

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]

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

thank you