CODEWITHSUNDEEP
google-sheetsfiltergoogle-sheets-formulavlookuparray-formulas
Harry
Harry

Reputation: 328

Search through Google form data

I have a Google Form that outputs data like this:

Buyers sheet's row example (for each buyer who filled the form):

Timestamp | User 1 | Product 1 | Price 1 | Product 2 | Price 2 | Product 3 | Price 3 ...

Timestamp | User 2 | Product 1 | Price 1 | Product 2 | Price 2 | Product 3 | Price 3 ...

And on another sheet called Bids:

Product 1 | Base Price

Product 2 | Base Price

Product 3 | Base Price

The result I'd like to achieve is getting all the bids for the different products and display next to each product the highest bid with the corresponding username. I've tried with lookupv and query but did not succeed, I'm not exactly an expert in Google Sheets (that's an euphemism).

Any ideas?

Here is an copy of the actual sheet: https://docs.google.com/spreadsheets/d/1bW_MlogHpXQk-_DZupE22No7KwUyZesqggsEdQaXPgw/edit?usp=sharing

With an example on the Bids sheet for computer of what I'm trying to get. Buyers is a sheet populated automatically from a Google form.

Upvotes: 1

Views: 314

Answers (1)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, SORT(SPLIT(QUERY(FLATTEN(IF(
 FILTER(Buyers!C2:1000, MOD(COLUMN(Buyers!C1:1)-1, 2)=0)<>"", 
 FILTER(Buyers!C2:1000, MOD(COLUMN(Buyers!C1:1)-1, 2)=0)&"×"&
 FILTER(Buyers!C2:1000, MOD(COLUMN(Buyers!C1:1), 2)=0)&"×"&Buyers!B2:B1000, )), 
 "where Col1 is not null"), "×"), 2, 0), {2, 3}, 0)))

enter image description here

Upvotes: 2

Related Questions