Here i will consider a simple database to look up values from. Considering employee details as an example. Currently considering only 4 fields and 4 values, there may be case where there are hundreds of records when you actually need to do VLOOKUP.
Suppose there is another sheet where you want designation in second column against the employee number.
Select the cell where you want to write the vlookup i.e. where you want the designation and click on the insert function button. Here i suggest that if you are new to formulas, use this to create or edit formulas rather than writing everything.
Select vlookup. A pop will appear for your inputs. There are four fields that you need to input amongst which 3 are mandatory and 1 is optional.
Lookup_value is nothing but the value you want to find in the database. It can be a value, reference or a string. Here in our case we need to lookup for employee no. We will give reference of A2.
Col_index_num is nothing but the column number that you want to get back into the current cell when a match is found.
Range lookup can have two values 0(false) or 1(true). 0 means exact match should be returned in vlookup or 1 means match should be approximate.
Press ok and you will see the value in your cell.
Since we need to get values for all the rows, we need to have similar formular in all cells. We will not write the formula again but simply copy the cell and paste in all the other cells.
Final Formula: =VLOOKUP(A2,'Emp Details'!$A$1:$D$5,4,0)
Tip: While writing a formula the fields that look bold are mandatory and the one which is not bold is optional.
Simple vlookup.
Suppose there is another sheet where you want designation in second column against the employee number.
Select the cell where you want to write the vlookup i.e. where you want the designation and click on the insert function button. Here i suggest that if you are new to formulas, use this to create or edit formulas rather than writing everything.
Select vlookup. A pop will appear for your inputs. There are four fields that you need to input amongst which 3 are mandatory and 1 is optional.
Lookup_value is nothing but the value you want to find in the database. It can be a value, reference or a string. Here in our case we need to lookup for employee no. We will give reference of A2.
Table_array is nothing but the table you want to lookup in. Here you need to make sure that your table is sorted by your first column and your first column is the field that you want to lookup for. i.e. in our case the table should be sorted by employee no and should be the first column in our table.
Also to make sure the table reference does not change when we copy our formula to other location, just press F4 once after selecting the table.
Col_index_num is nothing but the column number that you want to get back into the current cell when a match is found.
Range lookup can have two values 0(false) or 1(true). 0 means exact match should be returned in vlookup or 1 means match should be approximate.
Press ok and you will see the value in your cell.
Since we need to get values for all the rows, we need to have similar formular in all cells. We will not write the formula again but simply copy the cell and paste in all the other cells.
Tip: While writing a formula the fields that look bold are mandatory and the one which is not bold is optional.
Simple vlookup.
vlookup help
No comments:
Post a Comment