CODEWITHSUNDEEP
google-sheetsgoogle-sheets-querygoogle-sheets-formula
Mathieu
Mathieu

Reputation: 4797

Google sheet - compare items based on another dataset and get the one with max value

I have a series of words in a column A, each associated with a certain number/score.

After this table of words/score, I have lines where I have values of these words and I run a contest and must get the item with the highest score.

Let's make this simple with this example:

enter image description here

Here my quesiton is about getting the blue value inside E8. That is: how to create a fomula which takes analyzes the contender of line 8 which are "word4 word5", "word1 word2", and "word2 word6" and for each of them goes on the column A to find it and find their associated score. And then put the name with the highest score on E8.

Note there here is a special attention for D7 which is "word2 word6" because there won't be a match on column A.

You'll see below the structure of my data and table: note that there I need to keep the comparison between strings/words on line 8 (and below) inside the column B, C, D and E.

Upvotes: 0

Views: 29

Answers (1)

player0
player0

Reputation: 1

=VLOOKUP(MAX(ARRAYFORMULA(IFERROR(VLOOKUP(TRANSPOSE(B8:D8), 
 A2:F7, 6, 0), ))), {ARRAYFORMULA(IFERROR(VLOOKUP(TRANSPOSE(B8:D8), 
 A2:F7, 6, 0), )), TRANSPOSE(B8:D8)}, 2, 0)

Upvotes: 1

Related Questions