Lookup - 2 Criteria - Item, and most recent entry - SUMMARY

[akbw1]

Hello!

I'm attempting to create a summary on a google sheets response form. There are multiple entries of the same item, but with an updated location for each entry.

What I would love, is for the formula to look up the item, search for the most recent entry (each row will have it's own auto-generated timestamp, and return the location.

I was thinking that HLookup might work for this, and I know if I just created a filter and sorted by date it would be able to look up the first entry, but I'm hoping for something that doesn't require me to re-sort manually.

Thanks!

 

[DILIPandey]

okay... share the sample data here.


Regards,
DILIPandey

 

[akbw1]

https://docs.google.com/spreadsheets/d/17Panc0mzYxaHqioGempXJyAhhum5qhMgC0AqQ4a7gIE/edit?usp=sharing

People "check in" the status of various pieces of equipment, which run along each row.

I'd like to summarize, searching for each individual piece of equipment, then reporting back it's current location based on the most recent entry.

Thanks!

 

[DILIPandey]

Okay... from where you will pick up Current Location, When, status ?

Better if you mention your expected results as well.


Regards,
DILIPandey

 

[akbw1]

Results will be summarized in the SUMMARY tab.

Current Location, When, Status - will be pulled from the FORM RESPONSES tab.

Each Response row contains a timestamp (A)

'SUMMARY'!B5:F11 (SPEAKER Equipment Summary), would search 'Form Responses 4'!E for the most recent entry (A) and return 'Form Responses 4'!F:H

'SUMMARY'!B12:F15 (STAND Equipment Summary), would search 'Form Responses 4'!I for the most recent entry (A) and return 'Form Responses 4'!J:L

and so on.

Ideally, I'd love for the Summary to ignore entries that return "No *** was used (you may skip the rest of the questions on this page)"

I hope that is more clear!!

Thank you!

 

[DILIPandey]

'SUMMARY'!B5:F11 (SPEAKER Equipment Summary), would search 'Form Responses 4'!E for the most recent entry (A) and return 'Form Responses 4'!F:H

Okay... based on above logic, below is most recent entry:-

[TABLE="width: 860"]
<colgroup><col width="172" span="5" style="width:129pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 172"]Timestamp[/TD]
[TD="class: xl66, width: 172"]SPEAKER[/TD]
[TD="class: xl66, width: 172"]WHERE[/TD]
[TD="class: xl66, width: 172"]CONDITION[/TD]
[TD="class: xl66, width: 172"]WHY?[/TD]
[/TR]
[TR]
[TD="class: xl67"]1/21/2016 13:31:31[/TD]
[TD="class: xl68"]Lenny Kravitz[/TD]
[TD="class: xl68"]Dalton's[/TD]
[TD="class: xl68"]Poor[/TD]
[TD="class: xl68"] [/TD]
[/TR]
</tbody>[/TABLE]

Now where this should appear i.e., which row of "Summary" sheet it should appear?

Also, how you will tell Excel that for 'SUMMARY'!B5:F11', return 'Form Responses 4'!F:H
and for 'SUMMARY'!B12:F15' return 'Form Responses 4'!J:L
?

Regards,
DILIPandey