IF() FUNCTION PRACTICE
Use the IF() Function to complete the STATUS column below
-- Modify the formula in the STATUS column to include the DUE DATE requirements
If the DUE DATE is past due append the text "PAST DUE" to the current Status using the CONCATENATE or CONCAT
function
PROJECTS DUE DATE PERCENTAGE STATUS DUE DATE STATUS
PROJECT A 12/9/2024 0.88 IN PROGRESS IN PROGRESS
PROJECT B 12/19/2024 1.00 COMPLETE COMPLETE
PROJECT C 12/4/2024 - NOT STARTED PAST DUE - NOT STARTED
PROJECT D 12/17/2024 0.07 IN PROGRESS IN PROGRESS
PROJECT E 12/8/2024 0.10 IN PROGRESS IN PROGRESS
PROJECT F 12/2/2024 1.00 COMPLETE PAST DUE - COMPLETE
PROJECT G 12/9/2024 0.95 IN PROGRESS IN PROGRESS
PROJECT H 12/19/2024 0.43 IN PROGRESS IN PROGRESS
PROJECT I 12/6/2024 - NOT STARTED PAST DUE - NOT STARTED
PROJECT J 12/19/2024 1.00 COMPLETE COMPLETE
PROJECT K 12/4/2024 0.44 IN PROGRESS PAST DUE - IN PROGRESS
BONUS POINTS
-- Use CONDITIONAL FORMATTING to format the ROW (RED) is the PROJECT IS PAST DUE
VLOOKUP() FUNCTION PRACTICE
-- Use the VLOOKUP() function to lookup information from the CUSTOMER INFO Worksheet. i.e. ADDRESS, CITY, REGION, etc...
-- Use cell B9 as the LOOKUP_VALUE in the VLOOKUP
-- Cell D9 has a completed VLOOKUP for reference
CUSTOMER ID COMPANY NAME ADDRESS CITY REGION
ALFKI Alfreds Futterkiste Obere Str. 57 Berlin
CONTACT NAME PHONE
Maria Anders 030-0074321
-- BONUS POINTS
-- Use CONDITIONAL FORMATTING to change the color of cell J8 when it matches a specific Country (i.e. "Canada")
POSTAL CODE COUNTRY
12209 Germany
Customer ID Company Name Contact Name Address
ALFKI Alfreds Futterkiste Maria Anders Obere Str. 57
ANATR Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222
ANTON Antonio Moreno Taquería Antonio Moreno Mataderos 2312
AROUT Around the Horn Thomas Hardy 120 Hanover Sq.
BERGS Berglunds snabbköp Christina Berglund Berguvsvägen 8
BLAUS Blauer See Delikatessen Hanna Moos Forsterstr. 57
BLONP Blondel père et fils Frédérique Citeaux 24, place Kléber
BOLID Bólido Comidas preparadas Martín Sommer C/ Araquil, 67
BONAP Bon app' Laurence Lebihan 12, rue des Bouchers
BOTTM Bottom-Dollar Markets Elizabeth Lincoln 23 Tsawassen Blvd.
BSBEV B's Beverages Victoria Ashworth Fauntleroy Circus
CACTU Cactus Comidas para llevar Patricio Simpson Cerrito 333
CENTC Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993
CHOPS Chop-suey Chinese Yang Wang Hauptstr. 29
COMMI Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23
CONSH Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery
DRACD Drachenblut Delikatessen Sven Ottlieb Walserweg 21
DUMON Du monde entier Janine Labrune 67, rue des Cinquante Otages
EASTC Eastern Connection Ann Devon 35 King George
ERNSH Ernst Handel Roland Mendel Kirchgasse 6
FAMIA Familia Arquibaldo Aria Cruz Rua Orós, 92
FISSA FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86
FOLIG Folies gourmandes Martine Rancé 184, chaussée de Tournai
FOLKO Folk och fä HB Maria Larsson Åkergatan 24
FRANK Frankenversand Peter Franken Berliner Platz 43
FRANR France restauration Carine Schmitt 54, rue Royale
FRANS Franchi S.p.A. Paolo Accorti Via Monte Bianco 34
FURIB Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32
GALED Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23
GODOS Godos Cocina Típica José Pedro Freyre C/ Romero, 33
GOURL Gourmet Lanchonetes André Fonseca Av. Brasil, 442
GREAL Great Lakes Food Market Howard Snyder 2732 Baker Blvd.
GROSR GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes
HANAR Hanari Carnes Mario Pontes Rua do Paço, 67
HILAA HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35
HUNGC Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St.
HUNGO Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road
ISLAT Island Trading Helen Bennett Garden House Crowther Way
KOENE Königlich Essen Philip Cramer Maubelstr. 90
LACOR La corne d'abondance Daniel Tonini 67, avenue de l'Europe
LAMAI La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine
LAUGB Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St.
LAZYK Lazy K Kountry Store John Steel 12 Orchestra Terrace
LEHMS Lehmanns Marktstand Renate Messner Magazinweg 7
LETSS Let's Stop N Shop Jaime Yorres 87 Polk St. Suite 5
LILAS LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo
LINOD LINO-Delicateses Felipe Izquierdo Ave. 5 de Mayo Porlamar
LONEP Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd.
MAGAA Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22
MAISD Maison Dewey Catherine Dewey Rue Joseph-Bens 532
MEREP Mère Paillarde Jean Fresnière 43 rue St. Laurent
MORGK Morgenstern Gesundkost Alexander Feuer Heerstr. 22
NORTS North/South Simon Crowther South House 300 Queensbridge
OCEAN Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A
OLDWO Old World Delicatessen Rene Phillips 2743 Bering St.
OTTIK Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369
PARIS Paris spécialités Marie Bertrand 265, boulevard Charonne
PERIC Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321
PICCO Piccolo und mehr Georg Pipps Geislweg 14
PRINI Princesa Isabel Vinhos Isabel de Castro Estrada da saúde n. 58
QUEDE Que Delícia Bernardo Batista Rua da Panificadora, 12
QUEEN Queen Cozinha Lúcia Carvalho Alameda dos Canàrios, 891
QUICK QUICK-Stop Horst Kloss Taucherstraße 10
RANCH Rancho grande Sergio Gutiérrez Av. del Libertador 900
RATTC Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr.
REGGC Reggiani Caseifici Maurizio Moroni Strada Provinciale 124
RICAR Ricardo Adocicados Janete Limeira Av. Copacabana, 267
RICSU Richter Supermarkt Michael Holz Grenzacherweg 237
ROMEY Romero y tomillo Alejandra Camino Gran Vía, 1
SANTG Santé Gourmet Jonas Bergulfsen Erling Skakkes gate 78
SAVEA Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln.
SEVES Seven Seas Imports Hari Kumar 90 Wadhurst Rd.
SIMOB Simons bistro Jytte Petersen Vinbæltet 34
SPECD Spécialités du monde Dominique Perrier 25, rue Lauriston
SPLIR Split Rail Beer & Ale Art Braunschweiger P.O. Box 555
SUPRD Suprêmes délices Pascale Cartrain Boulevard
89 JeffersonTirou,
Way255
THEBI The Big Cheese Liz Nixon Suite 2
THECR The Cracker Box Liu Wong 55 Grizzly Peak Rd.
TOMSP Toms Spezialitäten Karin Josephs Luisenstr. 48
TORTU Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123
TRADH Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414
TRAIH Trail's Head Gourmet Provisioners Helvetius Nagy 722 DaVinci Blvd.
VAFFE Vaffeljernet Palle Ibsen Smagsløget 45
VICTE Victuailles en stock Mary Saveley 2, rue du Commerce
VINET Vins et alcools Chevalier Paul Henriot 59 rue de l'Abbaye
WANDK Die Wandernde Kuh Rita Müller Adenauerallee 900
WARTH Wartian Herkku Pirkko Koskitalo Torikatu 38
WELLI Wellington Importadora Paula Parente Rua do Mercado, 12
WHITC White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B
WILMK Wilman Kala Matti Karttunen Keskuskatu 45
WOLZA Wolski Zajazd Zbyszek Piestrzeniewicz ul. Filtrowa 68
City Region Postal Code Country Phone
Berlin 12209 Germany 030-0074321
México D.F. 05021 Mexico (5) 555-4729
México D.F. 05023 Mexico (5) 555-3932
London WA1 1DP UK (171) 555-7788
Luleå S-958 22 Sweden 0921-12 34 65
Mannheim 68306 Germany 0621-08460
Strasbourg 67000 France 88.60.15.31
Madrid 28023 Spain (91) 555 22 82
Marseille 13008 France 91.24.45.40
Tsawassen BC T2F 8M4 Canada (604) 555-4729
London EC2 5NT UK (171) 555-1212
Buenos Aires 1010 Argentina (1) 135-5555
México D.F. 05022 Mexico (5) 555-3392
Bern 3012 Switzerland 0452-076545
São Paulo SP 05432-043 Brazil (11) 555-7647
London WX1 6LT UK (171) 555-2282
Aachen 52066 Germany 0241-039123
Nantes 44000 France 40.67.88.88
London WX3 6FW UK (171) 555-0297
Graz 8010 Austria 7675-3425
São Paulo SP 05442-030 Brazil (11) 555-9857
Madrid 28034 Spain (91) 555 94 44
Lille 59000 France 20.16.10.16
Bräcke S-844 67 Sweden 0695-34 67 21
München 80805 Germany 089-0877310
Nantes 44000 France 40.32.21.21
Torino 10100 Italy 011-4988260
Lisboa 1675 Portugal (1) 354-2534
Barcelona 08022 Spain (93) 203 4560
Sevilla 41101 Spain (95) 555 82 82
Campinas SP 04876-786 Brazil (11) 555-9482
Eugene OR 97403 USA (503) 555-7555
Caracas DF 1081 Venezuela (2) 283-2951
Rio de Janeiro RJ 05454-876 Brazil (21) 555-0091
San Cristóbal Táchira 5022 Venezuela (5) 555-1340
Elgin OR 97827 USA (503) 555-6874
Cork Co. Cork Ireland 2967 542
Cowes Isle of Wight PO31 7PJ UK (198) 555-8888
Brandenburg 14776 Germany 0555-09876
Versailles 78000 France 30.59.84.10
Toulouse 31000 France 61.77.61.10
Vancouver BC V3F 2K1 Canada (604) 555-3392
Walla Walla WA 99362 USA (509) 555-7969
Frankfurt a.M. 60528 Germany 069-0245984
San Francisco CA 94117 USA (415) 555-5938
Barquisimeto Lara 3508 Venezuela (9) 331-6954
I. de Margarita Nueva Esparta 4980 Venezuela (8) 34-56-12
Portland OR 97219 USA (503) 555-9573
Bergamo 24100 Italy 035-640230
Bruxelles B-1180 Belgium (02) 201 24 67
Montréal Québec H1J 1C3 Canada (514) 555-8054
Leipzig 04179 Germany 0342-023176
London SW7 1RZ UK (171) 555-7733
Buenos Aires 1010 Argentina (1) 135-5333
Anchorage AK 99508 USA (907) 555-7584
Köln 50739 Germany 0221-0644327
Paris 75012 France (1) 42.34.22.66
México D.F. 05033 Mexico (5) 552-3745
Salzburg 5020 Austria 6562-9722
Lisboa 1756 Portugal (1) 356-5634
Rio de Janeiro RJ 02389-673 Brazil (21) 555-4252
São Paulo SP 05487-020 Brazil (11) 555-1189
Cunewalde 01307 Germany 0372-035188
Buenos Aires 1010 Argentina (1) 123-5555
Albuquerque NM 87110 USA (505) 555-5939
Reggio Emilia 42100 Italy 0522-556721
Rio de Janeiro RJ 02389-890 Brazil (21) 555-3412
Genève 1203 Switzerland 0897-034214
Madrid 28001 Spain (91) 745 6200
Stavern 4110 Norway 07-98 92 35
Boise ID 83720 USA (208) 555-8097
London OX15 4NB UK (171) 555-1717
København 1734 Denmark 31 12 34 56
Paris 75016 France (1) 47.55.60.10
Lander WY 82520 USA (307) 555-4680
Charleroi B-6000 Belgium (071) 23 67 22 20
Portland OR 97201 USA (503) 555-3612
Butte MT 59801 USA (406) 555-5834
Münster 44087 Germany 0251-031259
México D.F. 05033 Mexico (5) 555-2933
São Paulo SP 05634-030 Brazil (11) 555-2167
Kirkland WA 98034 USA (206) 555-8257
Århus 8200 Denmark 86 21 32 43
Lyon 69004 France 78.32.54.86
Reims 51100 France 26.47.15.10
Stuttgart 70563 Germany 0711-020361
Oulu 90110 Finland 981-443655
Resende SP 08737-363 Brazil (14) 555-8122
Seattle WA 98128 USA (206) 555-4112
Helsinki 21240 Finland 90-224 8858
Warszawa 01-012 Poland (26) 642-7012
Customer ID Company Name Contact Name Address City
ALFKI Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin
ANATR Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. -- Create a
proper col
ANTON Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F.
AROUT Around the Horn Thomas Hardy 120 Hanover Sq. London
BERGS Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå
BLAUS Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim
BLONP Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg
BOLID Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid
BONAP Bon app' Laurence Lebihan 12, rue des Bouchers Marseille
BOTTM Bottom-Dollar Markets Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen
BSBEV B's Beverages Victoria Ashworth Fauntleroy Circus London
CACTU Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires
CENTC Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F.
CHOPS Chop-suey Chinese Yang Wang Hauptstr. 29 Bern
COMMI Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23 São Paulo
CONSH Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London
DRACD Drachenblut Delikatessen Sven Ottlieb Walserweg 21 Aachen
DUMON Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes
EASTC Eastern Connection Ann Devon 35 King George London
ERNSH Ernst Handel Roland Mendel Kirchgasse 6 Graz
FAMIA Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo
FISSA FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid
FOLIG Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille
FOLKO Folk och fä HB Maria Larsson Åkergatan 24 Bräcke
FRANK Frankenversand Peter Franken Berliner Platz 43 München
FRANR France restauration Carine Schmitt 54, rue Royale Nantes
FRANS Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino
FURIB Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa
GALED Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona
GODOS Godos Cocina Típica José Pedro Freyre C/ Romero, 33 Sevilla
GOURL Gourmet Lanchonetes André Fonseca Av. Brasil, 442 Campinas
GREAL Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene
GROSR GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas
HANAR Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro
HILAA HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal
HUNGC Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin
HUNGO Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork
ISLAT Island Trading Helen Bennett Garden House Crowther Way Cowes
KOENE Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg
LACOR La corne d'abondance Daniel Tonini 67, avenue de l'Europe Versailles
LAMAI La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine Toulouse
LAUGB Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver
LAZYK Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla
LEHMS Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M.
LETSS Let's Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco
LILAS LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto
LINOD LINO-Delicateses Felipe Izquierdo Ave. 5 de Mayo Porlamar I. de Margarita
LONEP Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd. Portland
MAGAA Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo
MAISD Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles
MEREP Mère Paillarde Jean Fresnière 43 rue St. Laurent Montréal
MORGK Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig
-- Create a macro to format the given data by creating borders and
proper color formatting as assigned in "Customer Info" Worksheet.
