+ Reply to Thread
Results 1 to 10 of 10

why my vlookup formula behaves strangely

  1. #1
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    499

    why my vlookup formula behaves strangely

    i have applied the vlookup formula .it brings data from d instead of the expected col c. on sheet named My_stock at G2 formula is typed.formula brings value of column D instead of C.I wanted to extract column data from C2:F2..please see the attached file.
    Attached Files Attached Files
    Last edited by sumesh56; 02-08-2020 at 10:41 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,468

    Re: why my vlookup formula behaves strangely

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,305

    Re: why my vlookup formula behaves strangely

    not sure why you are using a vlookup like that, maybe you want this instead...
    =VLOOKUP($B2,data!$A$2:$C$126,3,FALSE)
    if you want to drag it toward the right without changing the 3 to a 4 and the column C to a D then this...
    =VLOOKUP($B2,data!$A$2:C$126,COLUMN(C$1),FALSE)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: why my vlookup formula behaves strangely

    The cell label in 'my stocks'!G1 is current price. The cell in data!A1:F1 labeled Current Price is C1. Your formula in cell 'my stocks'!G2 is

    =VLOOKUP($B2,data,COLUMNS($C2:F2),FALSE)

    The name data refers to data!A2:F126. However, COLUMNS($C2:F2) returns 4, so points to cells in data!D2:D126.

    I'd guess you'd have a lot more formulas in my stocks col G, and there's no good reason to calculate the column index in each of those cells. If it were me, I'd insert a new row 2 in my stocks, then determine the column numbers this way.

    G2: =MATCH("*current*",data!$A$1:$F$1,0)
    H2: =MATCH("*low*",data!$A$1:$F$1,0)
    I2: =MATCH("*high*",data!$A$1:$F$1,0)
    J2: =MATCH("*volume*",data!$A$1:$F$1,0)

    Then change the formulas in G3:J3 to

    G3: =VLOOKUP($B3,data,G$2,FALSE)
    H3: =VLOOKUP($B3,data,H$2,FALSE)
    I3: =VLOOKUP($B3,data,I$2,FALSE)
    J3: =VLOOKUP($B3,data,J$2,FALSE)

    Then fill G3:J3 down as far as needed. Your problem is a classic off-by-one error. I figure what you meant to use was COLUMNS($A2:C2) in the original 'my stocks'!G2 formula; however, it'd still be better to use an extra row to calculate the column index ONCE for all formulas which would use the same column number. For that matter, you'd also be better off using an array formula for G:J results. Select G3:J3, type =VLOOKUP(B3,data,G$2:J$2,0), hold down [Ctrl] and [Shift] keys and press [Enter]. If you want to avoid array formulas, INDEX+MATCH would be more efficient than multiple lookups for col B values. Since you're not using col A for anything,

    A3: =MATCH(B3,INDEX(data,0,1),0)

    Fill A3 down into A4:A72. Then

    G3: =INDEX(data,$A3,G$2)

    Fill G3 right into H3:J3, then select G3:J3 and fill down into G4:J72.
    Last edited by hrlngrv; 02-09-2020 at 12:23 AM.

  5. #5
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    499

    Re: why my vlookup formula behaves strangely

    Quote Originally Posted by protonLeah View Post
    Please Login or Register  to view this content.
    Thanks for the suggestion. it works with the given file. Actually the file was edited one. Now i am uploading the original one in which cell G2 contains your formula but it does not give the results.please see the attachemnt
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    499

    Re: why my vlookup formula behaves strangely

    Quote Originally Posted by Sambo kid View Post
    not sure why you are using a vlookup like that, maybe you want this instead...
    =VLOOKUP($B2,data!$A$2:$C$126,3,FALSE)
    if you want to drag it toward the right without changing the 3 to a 4 and the column C to a D then this...
    =VLOOKUP($B2,data!$A$2:C$126,COLUMN(C$1),FALSE)
    thanks for the suggestion . it works . kindly see the new attachment and suggest.

  7. #7
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    499

    Re: why my vlookup formula behaves strangely

    @ hrlngrv
    thanks for the suggestion.

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,305

    Re: why my vlookup formula behaves strangely

    the problem as I see it is ITC is what you are looking up, your named range for "data" begins in column A of the data tab. ITC is in column B of the data tab. I admit that I don't use named ranges as I find they can be confusing when you are trying to review a formula for what it does and what it targets.
    Some people do a named range for each column in their tabs so that they could target say, "symbol" (column B of the data tab) and that would be the location the vlookup would start.
    But in the absence of changing or adding additional named ranges, I would change it to this =VLOOKUP($C2,data!$B$1:I$137,COLUMN(C$1),FALSE) to return the current price. Using column C$1 targets the third column in your lookup table and allows you to drag it toward the right and return D next (the equivalent of 4) etc. if necessary.

  9. #9
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    499

    Re: why my vlookup formula behaves strangely

    Sambo kid
    thanks for the suggestion. it works fine.



    I would like to ask one more thing. please reply.

    As you can see the date in the file is 03 feb 20.
    I want the date to be displayed as 03-02-2020.
    I tried format cell-date-custom- and selected dd-mm-yyyy. but it is not been changed.
    Last edited by sumesh56; 02-13-2020 at 09:25 PM.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,601

    Re: why my vlookup formula behaves strangely

    Try the following:
    1. Select F2:F137
    2. Select Text to Columns on the Data tab
    3. Select Next > Next > and then DMY for the column data format
    4. Select Finish
    5. Now select dd-mm-yyyy as the custom format and then OK.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vlookup behaving strangely
    By Sly1980 in forum Excel General
    Replies: 6
    Last Post: 02-10-2017, 10:09 AM
  2. CSV format behaves strangely.
    By rkhyd in forum Excel General
    Replies: 3
    Last Post: 11-14-2014, 09:06 AM
  3. [SOLVED] Inherited formula uses multiplication strangely
    By meierrain in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2014, 02:31 PM
  4. IF formula behaving strangely in certain cells
    By gramomster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 12:15 PM
  5. Formula behaves differently
    By mtpsuresh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-22-2006, 07:14 AM
  6. [SOLVED] Office2000: Conditional format behaves strangely
    By Arvi Laanemets in forum Excel General
    Replies: 1
    Last Post: 04-07-2005, 04:09 AM
  7. [SOLVED] Win98 SE, Office 2000: ODBC from Excel behaves strangely on one computer
    By Arvi Laanemets in forum Excel General
    Replies: 0
    Last Post: 03-14-2005, 08:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1