Formula works in Excel but not Google Spreadsheet - IFERROR using a VLOOKUP

Question

The below information I was able to get to work find in Excel but cannot get it to work in Google Spreadsheets.

This is the formula that works in Excel :

=IFERROR(VLOOKUP(B2,{1,25;2,50;3,75},2),0)*IF(VLOOKUP(A2,Sheet2!$A$2:$B$5,2,0)="T",2,1)

I am trying to get a cell to calculate points for me based on a few criteria.

Points:

• Qty of 1 = 25 points
• Qty of 2 = 50 points
• Qty of 3 = 75 points.

Team members do not receive points for more than a quantity of 3. The quantities still get recorded but no additional points.

If team member has a T in the next column then they get double the points

If team member has a O in the next column then they get standard points.

On Sheet 1, the "Team Member" is picked from a drop list in Sheet 2 from the "Team Member" names

Sheet1:

        A            B        C
1   Team Member   Quantity  Points
2   Josh           1
3   Jo             2

Sheet2:

        A            B
1   Team Member   Code
2   Josh           T
3   Nate           T
4   Jo             O
5   Daniel         O

Answer 1

I believe that in Sheet1 Jo should be in A3 instead, right?

And in B2, there should be 1, 2 or 3 (or something else that yields 0 points).

If that is so, you can use CHOOSE instead of VLOOKUP:

=IFERROR(CHOOSE(B2,25,50,75),0)*IF(VLOOKUP(A2,Sheet2!$A$2:$B$5,2,0)="T",2,1)

Apparently, VLOOKUP in GoogleSpreadsheet cannot take an array in that form, but only a range, meaning that if you make a table somewhere with the point distributions, it will work.

---

EDIT: Since there can be quantities above 3, I'd suggest this modification:

=IFERROR(CHOOSE(MIN(B2,3),25,50,75),0)*IF(VLOOKUP(A2,Sheet2!$A$2:$B$5,2,0)="T",2,1)