Vlookup is extremely useful when you want to combine data from multiple spreadsheets.
In the vlokup.xls file below I use VLOOKUP to look up the phone numbers, and addresses and combine them into a single workbook.
Things to note, the key field has to be in the first column in the worksheet that contains the lookup value
the word false in the formula =VLOOKUP(C2,Phone!$A:$D,4,FALSE) means return an exact match it is possible to rerun a match from a range of values see below for the Markbook example
=RIGHT(A2,(LEN(A2)-2))
strip the first 2 characters from a string
Public Function RegExFind(str As String, pat As String) As Boolean
'Define the regular expression object
Dim RegEx As New RegExp
'Set up regular expression properties
With RegEx
.Global = False 'All occurences are not necessary since a single occurence is enough
.IgnoreCase = True 'No case-sensitivty
.MultiLine = True 'Check all lines
.Pattern = pat 'pattern
End With
RegExFind = RegEx.Test(str) 'Return the test result
End Function
Public Function RegExReplace(str As String, pat As String, replaceStr As String) As String
'Define the regular expression object
Dim RegEx As New RegExp
'Set up regular expression properties
With RegEx
.Global = False 'All occurences are not necessary since a single occurence is enough
.IgnoreCase = True 'No case-sensitivty
.MultiLine = True 'Check all lines
.Pattern = pat 'pattern
End With
RegExReplace = RegEx.Replace(str, replaceStr) 'Return the modified string with replacement value
Alt and type 0176 on Windows with a numeric keypad. ° Degree Symbol
=6371 * ACOS(SIN([latitude of 1st location]*PI()/180)*SIN([latitude of 2nd location]*PI()/180) + COS([latitude of 1st location]*PI()/180) * COS([latitude of 2nd location]*PI()/180)*COS([longitude of 2nd location]* PI()/180-[longitude of 1st location] *PI()/180))
The formula is relatively long, but once executed, you get precise distance in kilometers. I will not go into explaining the details of the formula, but one thing I will call out: Note that the first number 6371 is Earth’s radius in kilometers. If you prefer to calculate in miles, then substitude the radius to 3959 miles.
XLOOKUP Notes
We want to populate the
Final Mark (Round up) column using data from another worksheet
The Xlookup formula is the best way to do this. it lets us take a value from one worksheet and look up (find) the matchig value in any column
in another worksheet and then return a vlaue from the same row in another column.
In this example we are reading the ID number from cell C2 and then finding that ID number is column D in a 2nd worksheet called Loading and
returning the Final Mark (Round up) from Column Q.
=XLOOKUP(C2,loading!$D:$D,loading!$Q:$Q)
The loading tab contains the following data:
we are only interested in Column D which contains the student numbers and column Q withic contains the Final Mark (Round up).
