At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
This is the final result we will create, a store invoice. Just type in the item code and quantity, and the item name, unit price, and subtotal appear automatically, pulling details from the price list in another sheet. This method is used in stores, offices, and warehouses. Every time two tables need to be connected through the same code. Learn once, and the result can be used repeatedly. Let's start. Hello, welcome to the Excel for Beginners tutorial, VLOOKUP, and Index.me. We will create a store invoice that automatically fills in the item name and price, just by typing the item code. This function is very useful for connecting two tables.
Step 1: Two sheets, price list and invoice
This file has two sheets. The price sheet contains the list of codes, item names, and prices. The invoice sheet contains the item code and the quantity purchased, while the item name, price, and subtotal are still empty. Our task is to retrieve the name and price from the price sheet, based on the item code. Press Ctrl Page Down and Ctrl Page Up to switch sheets.
Step 2: VLOOKUP. value to find and reference table
Switch to the invoice sheet, then click cell B2. Type equals VLOOKUP, open the parenthesis. The first part is the value to look for, which is A2, the item code. Semicolon. The second part is the table where to look, which is the price sheet, from A2 to C9. Type a dollar sign before each letter and number to lock the table address, or press F4 for a quick way.
Step 3: Column number and exact search
Semicolon. The third part is the column number to retrieve. Column one is the code, column two is the item name, so type two. Semicolon. Fourth part determines the search must be exactly the same, type false. Close parenthesis and press Enter. The item name appears automatically.
Step 4: Copy the formula to all rows
Double-click fill handle to copy the formula to all rows. Now all codes have turned into item names.
Step 5: Price and subtotal
For price, the formula is almost the same. In C2, type VLOOKUP, A2, semicolon, same table, semicolon, but column number is 3, semicolon, false. Copy down. Then in E2, subtotal is price times quantity. Type equals C2 times D2, and copy down.
Step 6: Total and IFERROR for wrong codes
In E9, sum all subtotals with same. Now let's test. Change one of the codes, for example to B999, which is not in the list. An error N/A appears. To make the display more polite, we wrap the formula with IFERROR. The formula, IFERROR, open parenthesis, VLOOKUP as before, semicolon, quote code not found.
Step 7: INDEX and MATCH as an alternative
Besides VLOOKUP, there is another more flexible way, which is combining INDEX and MATCH. In the second cell, type equals INDEX, open parenthesis, column item name in the price sheet, semicolon, MATCH, open parenthesis, A2, semicolon, column code in the price sheet, semicolon, zero, then close two parentheses. MATCH finds the row order of the item code, and INDEX takes the cell content at that order. The result column is free, can be to the left or right of the code column. The result is the same as VLOOKUP.
Step 8: Clean up and save
Finally, we tidy up with currency format for price and subtotal, widen columns, then save with Ctrl+S. Now we have an invoice that fills itself. If there's a new item, just add one row in the price sheet, and the invoice will recognize it automatically. No more searching for prices one by one, no more typos in item names. Work that used to take hours is now completed in seconds, and the results are always consistent.
Done! The invoice now fills itself
Done! We've created an automatic invoice using VLOOKUP, IP error, and index MI. Thank you for watching, see you in other tutorial series. Want to master Excel for work and business? Cerdas Inovasi Digital offers Excel and Office courses, template creation services, reports, and dashboards, as well as sells the practice files for this tutorial. Contact us via WhatsApp, or visit cerdasinovasidigital.com. Thank you for watching, see you in other tutorial series. Thank you for watching!
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
53 video classes1,281 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
