Chapter 3.3

web queries skill refresher

1.Click a cell location on a worksheet where the first column of the data being imported should appear. 2.Click the Data tab of the Ribbon. 3.Click the From Web button in the Get External Data group of commands. 4.Enter a website address in the Address input box on the New Web Query dialog box and press the ENTER key on your keyboard. 5.Use the scroll bars and website links to navigate the website. 6.Select one or more yellow tags that contain the block or blocks of data you wish to import. 7.Click the Import button at the bottom of the New Web Query dialog box. 8.Click the Properties button at the bottom of the Import Data dialog box. 9.Make any necessary changes on the External Data Range Properties dialog box. 10.Click the OK button at the bottom of the External Data Range Properties dialog box. 11.Click the OK button at the bottom of the Import Data dialog box.

KLOOKUP function skill refresher

1.Type an equal sign (=). 2.Type the function name HLOOKUP followed by an open parenthesis ((). 3.Define the Lookup_value argument with a cell location, number, or text that will be searched in another workbook or worksheet. Text must be placed in quotation marks. 4.Type a comma. 5.Define the Table_array argument with a range of cells that contain the lookup value in the first row along with data that is to be displayed or used by the function. 6.Type a comma. 7.Define the Row_index_num argument with a number that designates the rows from the top of the table array range that will be displayed by the function. Count the first row of the table array range as 1. 8.Type a comma. 9.Define the Range_lookup argument with either the word FALSE or the word TRUE. The word FALSE will search for exact matches to the lookup value. The word TRUE will search for an exact or closest match less than the lookup value. Excel will define this argument as TRUE if it is omitted. 10.Type a closing parenthesis ()). 11.Press the ENTER key on your keyboard.

Vlookup function skill refresher

1.Type an equal sign (=). 2.Type the function name VLOOKUP followed by an open parenthesis ((). 3.Define the Lookup_value argument with a cell location, number, or text that will be searched in another workbook or worksheet. Text must be placed in quotation marks. 4.Type a comma. 5.Define the Table_array argument with a range of cells that contain the lookup value in the first column along with data that is to be displayed or used by the function. 6.Type a comma. 7.Define the Col_index_num argument with a number that designates the columns to the right in the table array range that will be displayed by the function. Count the first column of the table array range as 1. 8.Type a comma. 9.Define the Range_lookup argument with either the word FALSE or the word TRUE. The word FALSE will search for exact matches to the lookup value. The word TRUE will search for an exact or closest match less than the lookup value. Excel will define this argument as TRUE if it is omitted. 10.Type a closing parenthesis ()). 11.Press the ENTER key on your keyboard.

Table_array

Range of cells that contain data you wish the HLOOKUP function to search though (Lookup_value) and display. This cell range must contain the criteria used to define the Lookup_value in the first row. For example, if the range A2:D15 is used to define this argument, the criteria used to define the Lookup_value argument must exist in Row 2.

WHy need to put an absolute reference

The table array range will change because of relative referencing once the function is pasted to new cell locations. This may result in an error output for either the VLOOKUP or HLOOKUP function.

[RAnge_lookup]

This argument is defined with either the word TRUE or the word FALSE. When this argument is defined with the word FALSE, the function will look for an exact match to the criteria used to define the Lookup_value argument in the first row of the table array range. It is important to note the function will search the entire range to find a match. If this argument is defined with the word TRUE, the function will look for a value that is an exact match or the closest match that is less than the lookup value. For example, if the lookup value is 80 and the highest value in the first row of the table array range is a 78, the function will consider 78 a match for the number 80. However, if the lookup value is 80 and the lowest number in the first row of the table array range is 85, the function will produce an error. This is because the number 80 and any value less than 80 do not exist in the first row of the table array range. It is important to note that if you define this argument with the word TRUE, the data in the table array range must be sorted based on the values in the first row in ascending order from left to right. This is because the function will stop searching for a match once the value in the first row exceeds the lookup value. If the data in the table array range is not sorted, the function can either produce an error code or display an erroneous result. This argument is in brackets because if it is not defined it will automatically be defined with the word TRUE.

Look up_value

This argument is typically defined with a cell location, number, or text. Text data must be enclosed in quotation marks for this argument. The function will search for the criteria entered into this argument in the first row of the range used to define the Table_array argument. For example, if the word Hat is used to define this argument, the function will search for the word Hat in the first row of the range used to define the Table_array argument.

Row_index_num

This is the row index number argument. It is defined with the number of rows below the first row in the range used to define the Table_array argument that contains the data you wish to display. For example, suppose the data you wish the function to display is contained in Row 5. If the range used to define the Table_array argument is A2:D15, then the column index number will be 4. Counting the rows below the first row in this range, Row 2 would be 1, Row 3 would be 2, Row 4 would be 3, and Row 5 would be 4. It is important to remember to count the first row in the table array range as 1.

2 look up functions

VLOOKUP and HLOOKUP

how is VLOOKUP function similar to Statisitcal If functions

Vlookup function is only looking for one specific cell location rather than multiple cells in the statisitical if functions

look up functions

are used to search for and display data located in an existing worksheet or in other worksheets/workbooks

If you are defining the e Range_lookup argument with the word TRUE for either the VLOOKUP or HLOOKUP function, the range used to define the Table_array argument must be sorted in

ascending order

Why is VLook up function named this

becasue the function wil lsearch vertically down the first column of range of cells to find what is called a lookup value

[Range_lookup]

defined with TRUE or FALSE FALSE: searches for an exact mact to the lookup value TRUE: searches for an exact match or the closest match that is less than the lookup value. Data must be sorted in ascending order. (smallest to largest)

look_up value

function will search for the criteria entered into this argumetn in the first column of the range used to define the Table_array argument

Hlookup function searches

hoizontially across the first row of the table array range, When the function finds a match for the lookup value, it will display the contents in a cell location based on a row index number. This number designates how many rows below the first row of the table array range the function should display

if you recieve the #N/A error code when using the VLOOKUP or HLOOKUP function,

it indicates that Excel cannot find the lookup value in the table array range -You may also see this error code if you copy and paste the function and forget to put an absolute reference on the range used to define the Table_array argument.

.For the HLOOKUP function, the table array range must be sorted from

left to right based on the values in the first row, from smallest to largest or A to Z.

Table_Array

range of cells that contain data the function will search through and display. This range must contain the criteria used to define the Lookup_value in the first column.

If you are copying and pasting a VLOOKUP or HLOOKUP function, you will most likely need to place an absolute reference on the

range used to define the Table_array argument

For VLOOKUP function, the table array range must be sorted from

smallest to largest or from A to Z based on the values in the first column

#REF! error code indicates

that the column index number or row index number exceeds the number of columns or rows in the range used to define the Table_array argument

Col_Index_Num

the column in the Table_array range that contains the data to display

VLOOKUP functions is typically used

to access and display data located in another worksheet/workbook to access and display data in the same worksheet

Hlookup function can be used

to display data from another worksheet/workbook

web queries allow you

to import external data from a website into an excel worksheet.