unable to apply the correct range to the column, "from which the values are accessed" by the VLOOKUP

[learningexcel]

I am unable to apply the correct range to the column, "from which the values are accessed" by the  command/ function.

So, what happens is that, there are the following scenarios obeserved, (for the column from which the value has to be extracted). Considering it to be the column B in the various scenarios, which contains 50 data points.

scenario 1: column B has 50 data points and a header as well (some heading, say Names of users). Hence the column B effectively strats from B2 and extends till B51. The 2 other columns also have the similar structure

Now when I apply the VLOOKUP formula of the type, say, =VLOOKUP(F1,A1:B51,2,0), I am able to get the correct values. I have not stated an example, as I know that the logic behind putting the final range of column B is somewhere going wrong when I do not get correct values (unlike this example) AND HENCE, I will be point this out in the next scenario, which I will be mentioning. Remark: Hence, iterested only in the Bold part.

scenario2:  Now, considering the other scenario, suppose none of the columns has headers and the colums effectively start from A1, F1, B1 etc.

When I put the VLOOKUP as, =VLOOKUP(F1,A1:B50,1,0), I smehow do not get the correct values/ In order to get the correct values I have to modify the function as: VLOOKUP(F1,A1:B50,2,0)  Remark: again interrested in the Bold part. Why to put the end range as 2, when B extends from B1 to B50?

Remark: Perhaps, I need to understand the correct concept, will be helpful if some one clarifies the basics behind the Vlookup cell ranges selection, with the reasons behind the selection.!

Regards

[#2]

Hello learningexcel,
I believe I understand your question but would you be able to provide an example file so that I can answer your question a little better?

[#3]

The table_array argument of your VLOOKUP consists of 2 columns, which are Column A and Column B. Your col_index_num argument is 2, which means that the result of your formula will come from the 2nd Column, which is Column B.

[#4]

@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

[#5]

@learningexcel 

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.

[#6]

@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

[#7 · Accepted solution]

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

[#8]

@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

[#9]

That is correct!

[#10]

Thank you so much @PRegan