1 / 20

Advanced Topics in Excel for School Assessment Coordinators

Learn how to use VLOOKUP to transfer data between spreadsheets and utilize SEARCH to find text strings in Excel. Master these functions to streamline your data processing tasks efficiently.

glenm
Download Presentation

Advanced Topics in Excel for School Assessment Coordinators

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced Topics in Excelfor School Assessment Coordinators Mr. Damian Aladro October 2019

  2. VLOOKUP FUNCTION IT ALLOWS YOU TO BRING INFORMATION FROM A SPREADSHEET IN A WORKBOOK TO ANOTHER SPREADSHEET WITHIN THE SAME WORKBOOK.

  3. VLOOKUP VLOOKUP SYNTAX =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) VALUE YOU SEARCH FOR REFERENCE SPREADSHEET WHERE YOU ARE LOOKING FOR THE lookup-value COLUMN NUMBER THAT CORRESPONDS TO THE FIELD YOU WANT TO TRANSFER TO THE MASTER SPREADSHEET

  4. VLOOKUP • BRING FLEID FROM REFERENCE LIST TO MASTER LIST • WE WILL USE Student ID to merge information • Student ID must be sorted • REMOVE Student IDs that are duplicated MASTER LIST REFERENCE LIST

  5. VLOOKUP MASTER LIST =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) IN CELL C2 FROM THE REFERENCE LIST TYPE THE FOLLOWING FORMULA =VLOOKUP(A1, 'REFERENCE SHEET’!$B$2:$E$4, 2,) REFERENCE LIST

  6. VLOOKUP MASTER LIST REFERENCE LIST

  7. VLOOKUP NOTICE THE FOLLOWING =VLOOKUP(A1, 'REFERENCE SHEET’!$B$2:$E$4, 2,) 1) DOLLAR SIGNS ARE USED BEFORE AND AFTER THE CELL LETTER RANGE. 2) NOT HAVING THESE DOLLAR SIGNS WILL MAKE THE FORMULA CHANGE THESE VALUES WHEN THE FORMULA IS EXTENDED. 3) THIS MAY RESULT IN NOT FINDING SOME OF THE VALUES.

  8. VLOOKUP NOTICE THE FOLLOWING =VLOOKUP(A1, 'REFERENCE SHEET’!$B$2:$E$4, 2,) WHEN MAKING THE RANGE SELECTION ON THE REFERENCE SPREADSHEET, MAKE SURE THE RANGE STARTS WITH THE COLUMN THAT CONTAINS THE VALUE(S) YOU ARE LOOKING FOR.

  9. VLOOKUP MASTER LIST EXTEND THE FORMULA USING THE FORMULA EXTENDER

  10. VLOOKUP MASTER LIST IT IS RECOMMENDED THAT YOU COPY AND PASTE VALUES ON COLUMN THAT HAS THE FORMULA (FOR THIS EXAMPLE COLUMN C). THIS WILL MAKE THESE CELLS CHANGE FROM THE FORMULA TO THE ACTUAL VALUE.

  11. VLOOKUP MASTER LIST IF YOU SEE #N/A, THIS MEANS THAT THE VALUE YOU ARE LOOKING FOR WAS NOT FOUND. THIS COULD BE DUE TO THE FACT THAT THE VALUE YOU ARE LOOKING FOR IS NOT ON THE REFERENCE SHEET OR THAT DOLLAR SIGNS WERE NOT USED IN THE FORMULA

  12. VLOOKUP https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

  13. SEARCH FUNCTION RETURNS THE LOCATION (POSITION AS A NUMBER) OF A TEXT STRING THAT IS FOUND WITHIN ANOTHER TEXT STRING

  14. SEARCH SEARCH SYNTAX =SEARCH( find_text, within_text, [start_num] ) THE TEXT STRING TO BE SEARCHED WITHIN TEXT STRING YOU WANT TO FIND FROM WHICH CHARACTER TO START IF OMITTED, IT SEARCHES FROM THE 1ST CHARACTER

  15. SEARCH SEARCH FOR THE WORD SCIENCE UNDER COURSE TITLE

  16. SEARCH IN CELL F2 TYPE THE FOLLOWING FORMULA =SEARCH(“Science”,E2,1)

  17. SEARCH EXTEND THE FORMULA USING THE FORMULA EXTENDER

  18. SEARCH #VALUE!: TEXT STRING YOU ARE SEARCHING WAS NOT FOUND NUMBER VALUE: TEXT STRING YOU ARE SEARCHING WAS FOUND

  19. SEARCH https://support.office.com/en-us/article/search-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513b495

  20. Mr. Damian Aladro daladro@dadeschools.net 305-823-1330 Ext 2111

More Related