Learn to use VLOOKUP in Excel
The VLOOKUP and HLOOKUP formula in Excel allow you look up a value in one column or row and return a corresponding value from a different column or row. VLOOKUP is a vertical lookup and HLOOKUP is a horizontal lookup
The table of data below contains 4 columns. A Product Number, the Supplier, the quantity in stock and the cost price. With a small table of data like this it is easy with the eye to look at a product number and see who the supplier is, how much is in stock and how much it cost. But imagine you had a large table of data, scrolling through it, or using the filters to find the sock code is not efficient. This is where VLOOKUPs are very powerful.
VLOOKUP Syntax
The syntax for VLOOKUP is: VLOOKUP = (lookup value, table array, column index, range lookup) Lookup value is the value you wish to look up. This value must be in the far most left column of the table Table array is the table in which you want to search Column index is the column number you wish to return the data from Range lookup offers a true or false selection. Where true is an exact match and will only return a value where an exact match is found. When using an Approx. match (false), items in the table must be sorted from lowest to highest, or in alphabetical order.Constructing a VLOOKUP formula
Using the above table of data, givenPosted from my blog with SteemPress : http://theexcelclub.com/learn-to-use-vlookup-in-excel/
What formula did you use to return the customer? =VLOOKUP(A8,A1:C4,2,FALSE)
What formula did you use to return the Invoice value? =VLOOKUP(A8,A1:C4,3,FALSE)
What would happen your formula if you entered a new column of data between the invoice number and the Customer in the lookup table? The value returned for Customer is unaffected, but the value returned for Invoice Value changes to 0. The formula would have to be updated to reflect the change in the table array and column index number.
What formula did you use to return the customer?
=VLOOKUP(A7,A1:C4,2,FALSE)
What formula did you use to return the Invoice value?
=VLOOKUP(A7,A1:C4,3,FALSE)
What would happen your formula if you entered a new column of data between the invoice number and the Customer in the lookup table?
if you entered a new column the vlookup would no longer work because the columns move so the column index number used in the formulas would no longer be correct
Nice work @dernan, you got all of this correct. There is actually a LOOKUP skills test available free on the website if you would like to try it out http://theexcelclub.com/excel-skills-testing/
Congratulations @theexcelclub! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :
Click here to view your Board
If you no longer want to receive notifications, reply to this comment with the word
STOPTo support your work, I also upvoted your post!
Congratulations @theexcelclub!
You raised your level and are now a Minnow!