How to update VLOOKUP results in Google Sheets to most recent input from Google form?

Question

I am trying to create a formula that pulls responses from a Google Form and organizes them into rows correlating with a client's name. The name is entered manually in and also appears in a drop-down in the form, so they should always match up.

I used =ARRAYFORMULA(IFERROR(VLOOKUP($A$3:$A,'Form Responses 1'!$B$2:$F, B2,FALSE ),"")), where:

• manually entered names are in column A
• the column I want results from is in row 2
• and the data being pulled is coming from sheet 'Form Responses 1'

In the sample, I have highlighted the data in the Form Responses that I am trying to get to show up for each name in the 'Current List' sheet.

Sample Sheet here

Answer 1

Try this:B3:

=ARRAYFORMULA(IFERROR(VLOOKUP($A$3:$A,SORT(FILTER({'Form Responses 1'!$B$2:$F$7,'Form Responses 1'!$A$2:$A$7},'Form Responses 1'!C2:C7<>""),6,0),B2,0)))

Drag fill to the right.

It looks big only because of the name of your sheet :( Otherwise, It's a simple blank filter and sorting by A( timestamp)

Edit: Formula simplified

Answer 2

I don't see an arrayformula solution for this. Here is a solution that requires one formula per cell on "Current List" sheet.

To look up contact phone numbers (column B on Current List):

=iferror(query('Form Responses 1'!$A:$F, "select C where B='" & $A3 & "' and C<>'' order by A desc limit 1 label C ''", 1))

The key part is the query string, which is repeated here for readability:

select C 
  where B = '" & $A3 & "' 
  and C <> '' 
  order by A desc 
  limit 1 
  label C ''

So, we select the entry from C where

• B matches the name in A3 of current sheet
• C is not empty
• The entry is the latest (order by decreasing timestamp, take the top result)

Labeling the result as empty string '' means we get single-cell output, as desired. The last parameter of query is the number of header rows in the source data.

The above formula can be copied down the row, so you get contact phone numbers for everyone.

For other text fields (address, pizza preference) the formula is adjusted by replacing C by another letter (D or E) in three places.

For the last field, which is not a text but a date, also replace the non-blank condition C<>'' with F is not null. (The former is used for text, the latter for numbers and dates).

For reference, this is the input data on Form Responses 1:

+--------------------+---------+--------------+-------------+--------------------+----------------------+
|     Timestamp      | Client  |   Contact    |   Address   | Do you like pizza? | Party Date Preferred |
+--------------------+---------+--------------+-------------+--------------------+----------------------+
| 9/20/2017 13:06:36 | Joe     | 555-555-5555 | 123 Main st | Yes                | 9/30/2017            |
| 9/20/2017 13:07:00 | Bob     | 555-111-1111 |             | No                 | 10/10/2017           |
| 9/20/2017 13:07:17 | Joe     |              |             | No                 | 10/27/2017           |
| 9/20/2017 13:07:43 | Jessica | 555-222-2222 | 700 Main St | Maybe              |                      |
| 9/20/2017 13:07:51 | Jessica |              |             |                    | 9/29/2017            |
| 9/20/2017 13:08:50 | Jessica | 555-123-1234 |             |                    |                      |
+--------------------+---------+--------------+-------------+--------------------+----------------------+

and this is the output:

+---------+--------------+-------------+--------------------+----------------------+
|         |   Contact    |   Address   | Do you like pizza? | Party Date Preferred |
+---------+--------------+-------------+--------------------+----------------------+
| Bob     | 555-111-1111 |             | No                 | 10/10/2017           |
| Jessica | 555-123-1234 | 700 Main St | Maybe              | 9/29/2017            |
| Joe     | 555-555-5555 | 123 Main st | No                 | 10/27/2017           |
+---------+--------------+-------------+--------------------+----------------------+