Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 382K Members
- 8,025 Online
- 400K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- unable to apply the correct range to the column, "from which the values are accessed" by the VLOOKUP

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Home
- :
- Excel
- :
- General Discussion
- :
- unable to apply the correct range to the column, "from which the values are accessed" by the VLOOKUP

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019 03:48 AM

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

Labels:

9 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019 07:04 AM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019 07:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-16-2019 09:42 PM

@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**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-17-2019 07:16 AM

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

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-25-2019 10:58 PM

@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**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2019 06:22 AM

SolutionHello @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

Best Response confirmed by
learningexcel (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2019 11:26 PM

@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**,

*=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**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-27-2019 05:44 AM

That is correct!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-29-2019 11:42 PM

Thank you so much @PRegan

Related Conversations

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
34.6K
Views

2 Likes

25 Replies

Early preview of Microsoft Edge group policies

Sean Lyndersay
in
Discussions
on
06-14-2019
14.9K
Views

20 Likes

64 Replies

*Updated* Syncing in Microsoft Edge Preview Channels

Elliot Kirk
in
Articles
on
05-01-2019
26.5K
Views

21 Likes

151 Replies

This form can't be distributed as it is asking for personal or sensitive information

NZLaSalle
in
Microsoft Forms
on
07-19-2019
3,974
Views

4 Likes

50 Replies

Microsoft Office 2019 Now Available – Comparing 2019 🆚 2016 🆚 365, New Features in Access & Excel

Dan Moorehead | PowerAccess
in
Access
on
09-24-2018
142K
Views

4 Likes

13 Replies

Teams Calling - Dial pad missing

Chris Cooper
in
Microsoft Teams
on
03-26-2019
20.2K
Views

0 Likes

71 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft