Since you cannot use the VLOOKUP function to do a leftward column lookup, use the MATCH and INDEX functions together to achieve this objective. Use the MATCH function to find the row number when looking in a column and then use this row reference nested within the INDEX function to get to the needed data.
- [Voiceover] On this worksheet called Master List,…we've got some Social Security numbers in column E…and we want to be able ultimately to come up…with the name associated with each one of these.…Now, one thought is, let's use a VLOOKUP.…A VLOOKUP function however is based on looking up…information in a column and then moving rightward…within related data to come up with an answer.…But let's say that we don't have the capability here…of moving these columns around.…Maybe there are multiple formulas in this workbook…and maybe in other workbooks, too that are linked…that look up data in this list.…
It could also be the case this data is protected.…We can't move it around at all.…Imagine that it's more or less frozen.…We can't touch it, although we can certainly…get data from it.…So, let's begin with the idea that if we need…to find out whose name is associated with this,…we first have to find out where it appears in column C.…Now imagine, too, that when we're doing this…we're working off of a different worksheet.…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome50s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Use MATCH and INDEX together