Logical and lookup functions Test your skills

IF functions allow for a maximum of three arguments.

We wish to give a gift to loyal members. In L7 create a calculation to put the value Gift in the cell if the attendee joined before 2016 or has attended 6 or more events, otherwise leave it blank. What is the total number of gifts required as shown in U24?

The organisers have decided that they cannot afford so many gifts, change the formula so that it only returns Gift if the attendee joined before 2016 and they have attended a minimum of 6 events. What is the updated number of gifts required as shown in U24?

Prices for the event are shown in V7:V8, people attending for just one day pay $350, but people attending more than one day pay $300 per day (i.e. someone attending 3 days pays $900). Column N tells us how many days they will be attending. Create a calculation in O7 that will check how many days they are attending and calculate the correct cost accordingly. What is the total cost for all attendees as shown in U25?

Discounts are awarded to loyal members. People who have attended 10 or more events get a 20% discount off the full price, people who have attended between 5 and 9 events (inclusive) get a $50 discount off the full price. If an attendee does not qualify for a discount, they pay full price.

In Column P calculate the price after discount, e.g. for someone attending for 1 day who has attended 6 events, the After Discount price will be $300. What is the total After Discount price as shown in U26?

Attendees are awarded a status based on how many events they have attended as shown in T19:U22. Attendees who have attended 1-9 events get Bronze, 10-19 Silver, 20-29 Gold and 30+ get Platinum. In column M use a calculation to determine each attendee’s status. What is the total number of Silver attendees as shown in V20?

When using an approximate (or Range) VLOOKUP the last argument is optional but when using an exact match VLOOKUP the last argument must be set to FALSE or 0.

Column F contains the organisation code for each attendee. Use this information to lookup the Organisation name from the list provided in the Lookup Lists sheet. (We suggest using defined names or tables to make this easier.) How many attendees are from Colot (shown in V15)?

The country code for each attendee is shown in Column H. Use this information to lookup the Country name from the list provided in the Lookup Lists sheet. (We suggest using defined names to make this easier.) How many attendees are from the United States or United Kingdom (shown in V16)?

In question 10 you had to look up the Country Name. This could be done using an XLOOKUP or a combination of VLOOKUP and MATCH.

There are 7 seating areas labelled A to G. Seats are allocated according to country of origin and type of meal as shown in the lookup matrix in the Lookup Lists sheet. E.g. a vegan from Armenia will be allocated to seating area A. Create a calculation in Column R to look up the correct seating area for each attendee. How many people are allocated to area F (as shown in U34)?