This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Excel (113 exercises)
Topic ==>	Lookup functions (10 exercises)
Level ==>	Average difficulty
Subject ==>	Excel training

How to use the XLOOKUP function to look up data from different columns/rows for plants data

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

Open the workbook in the above folder. To do this exercise you may find it useful to create the following range names:

If you know about range names, you'll find it useful to use the labels in the top row to create 5 ranges called Plant_Name, Colour, Category, Soil and Price respectively.

Create an XLOOKUP formula to show the colour and price of any plant name you type in to cell H4:

You should look up the plant name in column A, and return the corresponding values in column B and E respectively. Use the fourth argument to control what happens if your plant name doesn't exist.

Now create a formula to fill in these values:

You should be able to create a single formula to give the colour of the plant name entered - this formula will spill over into the neighbouring cells to give the category, soil type and price of the plant.

You may find it useful to create a name for the following range to do this: