Connect Data from Multiple Reports
The VLOOKUP enables you to create lookup tables in Excel that can map one value to another. This function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row from the other table.
To use VLOOKUP, you supply 4 pieces of information:
The value you are looking for (lookup_value)
The range of cells that make up the table (table_array)
The number of the column from which to retrieve a result (column_index)
The match mode (range_lookup, TRUE = approximate, FALSE = exact)
Remember!
In exact match mode, if a lookup column contains duplicates, VLOOKUP will match the first value only
If you have existing VLOOKUP formulas in a worksheet, formulas may break if you insert a column in the table. This is because hard-coded column index values don't change automatically when columns are inserted or deleted.
The easiest way to trap errors with VLOOKUP is to wrap VLOOKUP in the IFERROR function. IFERROR allows you to "catch" any error and return a result of your choosing.
*Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.
COMET VLOOKUP Example
For example, you want to see the list of Players who have confirmed registration and their Passport expiration date.
Step 1: Export the Person registrations report and open it in Excel. Repeat the same action for Person identification documents report.
Step 2: Click on the cell A1 in the Person identification documents excel and press Shift + CTRL + right arrow® + down arrow ¯ to select the whole table. Then copy the data by clicking CTRL + C.
Step 3: Open the Person registrations excel and paste the table in the new Sheet by clicking the + sheet in the bottom left corner. In the example below, Sheet 1 is renamed to Person ID.
Step 4: Switch to the Data sheet, and add new Column, e.g. Passport expiration date
Step 5: Insert the Vlookup formula: =VLOOKUP(A2;'Person ID'!A:D;4; FALSE) :
A2 is the common unique information in both sheets, in our example COMET Person ID
'Person ID'!A:D; is the range of the cells that contain the information we are looking for
4 is the column order number for the information we are looking for, in our example Passport expiration date
False is the match mode that looks only for exact matches
Step 6: Click Enter and select the + in the bottom right corner of the cell to apply the formula to all other rows.
Step 7: Wrap VLOOKUP in the IFERROR function by updating your formula =IFERROR(VLOOKUP(A2;'Person ID'!A:D;4; FALSE); "Not found")
Please check the video tutorial: https://youtu.be/Zu4L1AjGq-Y