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.

Please Login or Register  to view this content.

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)

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.

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.

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.

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.