Forum Discussion
unable to apply the correct range to the column, "from which the values are accessed" by the VLOOKUP
Hello learningexcel,
You would simply adjust your Lookup_value and Table_array accordingly. So cell E1 would contain the formula =Vlookup(D1,$A$1:$B$13,2,FALSE).
The 2 in this formula comes from the fact that you want to return the market value of the company that you are looking up. The market value in your Table_array is located in column 2 (the companies are located in column 1). This is where the 2 comes from.
Hopefully this all makes sense to you. Let me know if there are any other questions!
PReagan
PReagan Hi, let's consider these two examples:
Now, as I have discussed in the question which I had posted, the other scenario can be thought of from this example only. That is, when the Fields or headings are not present.
That is, when the column entries actually start from A1, B1, D1 and we start applying the VLOOKUP formula in E1 suppose, so that the values are obtained in E1, E2 ...so on.
Remark: The doubt remains the same, as was asked in the question which had been posted by me. You can now answer it assuming 14 sets of data points, for which the corresponding values need to be found using the VLOOKUP. The understanding needs to be made about the difference in the formulae, which are applied in the 2 scenarios, that is,
1) the one posted above. and
2)when the fields or headings are not mentioned. ! (ie column 1 , market value etc are not mentioned and the entries effectively start from A1, B1 etc)
Regards
Hello,
If you would like to apply the VLOOKUP() formula to cell E2, your formula would look like this:
=VLOOKUP(D2,$A$2:$B$14,2,0)
This formula returns the value 541.5
How this formula works:
=VLOOKUP(Step 1, Step 2, Step 3, Step 4)
Step 1: Lookup_value - This is the value to be found in the first column of the table. Vlookup is a great formula to use when trying to lookup values but its kryptonite is that it can't lookup values to the left. So, your lookup value in Step 1 must be in the first column of the table or range that you are attempting to retrieve data from. Our lookup value is D2.
Step 2: Table_array - This is the table or range in which the data that you are searching for can be retrieved from. Our range is $A$2:$B$14.
Step 3: Col_index_num - This is the column number from the Table_array value in which the data you seek is retrieved from. Keep in mind that the Lookup_value is in column 1. Count how many columns to the right that your desired results is from your Lookup_value. Our desired result is one column to the right of the lookup_value, therefore our column number is 2.
Step 4: Range_lookup - Choose whether you want to find the closest match to your lookup_value or find an exact match. Enter 1 (TRUE) for the closest match; Enter 0 (FALSE) for an exact match. We want an exact match so our range_lookup is 0.
Hope this helps!
Try this out for yourself in cell E10 and post your formula.
PReagan Thank you for taking out time and posting the answer. However, I still have some doubts and if you can help in clearing up those:
1) How would the formula in the cell E1 look like , had there been "no headings for the data in Column A and Column B", that is, the headings, namely "Column 1 and Market value" were not present and the data actually started from the cells A1 and B1, extending till A13 AND B13 respectively. Also, suppose the that the data for which the values need to be found started from D1 and extended till D9.
Lastly, did not understand the significance of the highlighted part of your earlier answer, particularly for the correct application of the formula. Please refer to the attched picture.
Regards.
Ps: once you help me clear the above concepts, will certainly post the answer to the exercise u had given me. Thanks
Hello learningexcel,
You would simply adjust your Lookup_value and Table_array accordingly. So cell E1 would contain the formula =Vlookup(D1,$A$1:$B$13,2,FALSE).
The 2 in this formula comes from the fact that you want to return the market value of the company that you are looking up. The market value in your Table_array is located in column 2 (the companies are located in column 1). This is where the 2 comes from.
Hopefully this all makes sense to you. Let me know if there are any other questions!
PReagan
PReagan Thanks a lot in making me underdstand the significance of that 2. However, just to clear up my last confusion, ( if you do not mind :) ) and to have a crystal clear concept, suppose if I introduce a "new column" with some values between the "column A" and "column B", that is "column 1" and "market value", and considering the original posted scenario (as in the below picture):
Now, when I apply Vlookup in the cell E2 (for the picture/ scenario attched), considering that a column has been introduced between column A and column B, thus effectively shifting the existing column B to column C, shouldn't my Vlook up in cell E2 look like:
=VLOOKUP(D2, A2:C14,3,0) OR VLOOKUP(D2, A2:C14,3,1) for the exact and approximate matches repectively.
Since a new column has been introduced, the "market value" column (which haS NOW SHIFTED TO COLUMNC) , from which the VLOOKUP function is actually searching and accessing the correct value has shifted to number 3,hence the Bold 3 in the above VLOOKUP formula, is that correct?
Regards,
Learningexcel
