Nikhil Bhat (Financial services) (133 Points)
19 September 2015From 1981-82 to now IT dept has given Index for LTCG. How to automatically pick up these values from XL shhet table.
S. Shiroor
(Others)
(1207 Points)
Replied 19 September 2015
Use the Index Function with Match Function
1) type 1981-82 to 2015-16 in cells say M1 to M35
2) type 100 to 1081 in cells besides it i.e N1 to N35
3) Allow data value as Lists for drop down for M1 to M35
say in cell A4 = Year of Purchase then
in cell c4 allow drop list . i.e Data -->Data validation-->select data valid
Validation critria seelct list . In source type =$m$1:$m$35 --> Ok
Now cell C4 can take values from 1981-82 , 1982-83 ........2015-16 as a drop dowm list
4) Now in cell d4 say type =INDEX($M$1:$N$35,MATCH(C4,M$1:M$35,0),2)
a) $m$1 is the start of the range till $n$35
b) C4 is the year selected from drop dowm list
c) m$1: m$35 is range where c4 lies. & 0 is for exact match
d) 2 is the second column (Index values) which is automatically filled for corresponding year selected in C4.
i Hope this is clear
Kiran. S
(CTO)
(745 Points)
Replied 20 September 2015
If the table consits of 3 Columns then give 3 for match function ??
Nikhil Bhat
(Financial services)
(133 Points)
Replied 20 September 2015
Whether S.no 3 and 4 should be repeated for
Improvement and then Sale of property.?
Praveen Kumar
(Student CA Final )
(16 Points)
Replied 20 September 2015
Praveen Kumar
(Student CA Final )
(16 Points)
Replied 20 September 2015
Nikhil Bhat
(Financial services)
(133 Points)
Replied 20 September 2015
Thats what I meant . copy and paste for s.no 3 and 4 for Improvment and Sale.
S. Shiroor
(Others)
(1207 Points)
Replied 20 September 2015
Uploaded the XL sheet . Any improvement is most welcome
Share it freely. XL version is 2010.
S. Shiroor
(Others)
(1207 Points)
Replied 20 September 2015
In s.no 4 you can use the Vlookup function instead of index and match as follows
=VLOOKUP(C4,$M$1:$N$35,2)
C4 is the year you picked from drop down list
$m1:$n$35 is the table range of values
2 is column number where you are looking for corresponding value for C4.
CAclubindia
