Google Sheets – Filter Row Containing Exact Word and Highest Value

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I have a table (Master sheet) in Google Sheets that looks something like this:

enter image description here

https://ethercalc.org/t8f0ze0kyx6a

I need to filter out an entire row of information on a separate sheet (Last Lesson) but only pertaining to someone's last lesson, eg. Sam and his third lesson. I've tried to add various different things to my filter option but with no success. My knowledge of Excel/Google Sheets is basic so if there is another way to go about this without the filter option do share.

Best Answer

You should use a formula combining several functions so as to achieve what you need. The working formula is the following: 

=ArrayFormula(IFERROR
     (VLOOKUP(UNIQUE(Master!A1:A20),
        QUERY({Master!A1:M15},"Select * Order By Col13 Desc"),
            {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13},FALSE)))

EDIT
In your comment you mention:

Last lesson: The plan is to have everything from row 10 and below and column E and onward grouped. When a name is chosen in row 3 it should pull the info of that person's last lesson (see Sam). Below this little form is a filtered info from the main list of all the lessons. I figured I could keep adding names in column N12 onward and create a filter for each name to return their last lesson from New lesson sheet. The list of names and lessons will expand! – Linda

Here is the link to a working sheet.

PS: From your original Master sheet I deleted the redundant rows.

Related Topic