Using Lookups The term ‘look up’ as used in the practical exams means to look up from a list. If you see that you have to use a LOOKUP you must select the most appropriate function from LOOKUP, HLOOKUP and VLOOKUP.
LOOKUP This is used to look up information using data in the first row or the first column of a range of cells and returns a relative value. For the purpose of the practical exams this is the least useful of the three lookups and therefore the least likely to be used.
HLOOKUP This is a function that performs a horizontal lookup of data. This should be used when the values that you wish to compare your data with are stored in a single row. The values to be looked up are stored in the rows below these cells.
The function used above can be written in English as: I will look in the (horizontal) rows B2 to H3 to see if I can find the value that is in B6 (5). If I find it I will put into cell C6 the value from the 2nd row (programmer)
=HLOOKUP(B6,$B$2:$H$3,2,FALSE) The HLOOKUP function to tell the computer that to look for the answer in rows
The value that you are looking for. Must be a relative value The range of cells where you are trying to find a value that matches the previous part of the function (in this case B6). Must be an absolute range.
This means that when the value has been matched, the answer comes from the second row. The match was found in row 2, so the value returned comes from the next row in the range (row 3)
BY including ‘FALSE’ you are asking the computer to only find EXACT matches to the value in B6
VLOOKUP This is a function that performs a vertical lookup of data. This should be used when the values that wish to compare your data with are stored in a single column. The values to be looked up are stored in the columns to the right of these cells. The lookup data can be stored either in the same file or in a different file.
The function used above can be written in English as: I will look in the vertical columns of the file Client.csv for the value contained in cell B3 of Tasks.csv (6). I will only look in the range A2:B8. If I find the answer in this range I will return the value from the second column as long as it is an exact match. So if I find it in A2 I will return the value in B2.
=VLOOKUP(B3,Client.csv!$A$2:$B$8,2,FALSE) The VLOOKUP function to tell the computer to look for the answer in columns.
The value that you are looking for; must be a relative value.
The file where you are looking for the matches to the contents of ‘B3’ Remember the exclamation mark.
The range on Client.csv where you are trying to find the match to B3. This must be absolute values.
The value from the second column is returned. So if the value is found in column A, the corresponding value in column be is returned.
BY including ‘FALSE’ you are asking the computer to only find EXACT matches to the value in B3
IF Statements Note: With both types of IF statements it is important to put quotes around any text. This isn’t needed when using numbers or cell references.
Basic IF Statements
=IF(B10>6,”Yellow”,”Red”) This means that if B10 is greater than 6 put the value “Yellow” into the cell containing this function. If B10 isn’t greater than 6 put the value “Red” into the cell.
Nested IF Statements
=IF(B10>6,"Yellow",IF(B10>4,"Green",IF(B10>=0,"Red","Orange"))) This means that if B10 is greater than 6 put the value “Yellow” into the cell, otherwise check to see if B10 is greater than 4; if it is, put the value “Green” into the cell, otherwise check to see if B10 is greater than or equal to 0; if it is put the value “Red” into the cell. If none of this is true, put the value “Orange” into the cell.