# why my vlookup formula behaves strangely

1. ## 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.

2. ## Re: why my vlookup formula behaves strangely

``Please Login or Register  to view this content.``

3. ## 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)

4. ## 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.

5. ## Re: why my vlookup formula behaves strangely

Originally Posted by protonLeah
``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

6. ## Re: why my vlookup formula behaves strangely

Originally Posted by Sambo kid
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. ## Re: why my vlookup formula behaves strangely

@ hrlngrv
thanks for the suggestion.

8. ## 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. ## Re: why my vlookup formula behaves strangely

Sambo kid
thanks for the suggestion. it works fine.

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.

10. ## 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.

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

#### 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