Google sheet function (sort by name)

Question

So I have two csv files, one is the all member information, one is a poll event result, which is like:

Member information:

[Member Name],[Member Tier]

Apple, Bronze

Banana, Silver

Cat, Gold

Poll event result:

[Member Name],[OptionA],[OptionB],[OptionC]

Apple,0,0,1

Banana,1,0,0

Cat,0,1,0

I want to do is weight the vote value with member's tier, for example, Cat is gold member so in this poll OptionB will win.

But the poll csv file is lack of member's tier parameter, so, I'm thinking to do a function like:

Create list "tier Bronze","tier Silver","tier Gold" in member information file, loop for everyone, if tier match, add to that list and then go to poll file, loop for everyone, if name match the name in tier list, mark them.

I'm not sure is this the right way, and how to do it, any help will be appreciated :^)

Answer 1

What you need is a vlookup function. Take 2 lists with member name (as ID) in first left column and tier in 2nd and then use vlookup.

Put vlookup on the right part of your poll table and write: =vlookup(ID location;range with poll results;2;false)

Then copy down vlookup formula

Or you can do it in more elegant way using Arrayformula:

=ArrayFormula(
ifna(
vlookup(E3:E;$B$3:$C;2;false)
)
)