Cell range
Closed
guna
-
Dec 6, 2009 at 11:27 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 9, 2009 at 05:04 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 9, 2009 at 05:04 AM
Related:
- Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names.
- My contacts list names - Guide
- Monk names - Guide
- What does the insert key do - Guide
- Best friend contact names with emojis - Guide
- Please insert the correct cd-rom nfs most wanted ✓ - Video Games Forum
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 7, 2009 at 06:06 AM
Dec 7, 2009 at 06:06 AM
in SHEET2 from A1 to B18 copy the required references like this
A B
1.5 m16:z16
2.2 m17:z17
3 m18:z18
4 m19:z19
5.5 m20:z20
7.5 M21:Z21
11 m22:z22
15 m23:z23
18.5 m24:z24
22 m25:z25
30 m26:z26
37 m27:z27
45 m28:z28
55 m29:z29
75 m30:z30
90 m31:z31
110 m32:z32
132 m33:z33
the data from column M to z from rows 16 to 33 IN SHEET1 FLLL up your data
now In sheet1 A1 or any other cell copy this formula
for e.g if B16 in sheet 1 is 1.50 then the formula above will give you the column no. counting from column M as 1,of entry 1.50 in M16:Z16
NOTE:
If the value in sheet 1 B16 is not available in the relevant row then the formula will given you %N/A that is not available
Note2
if the number in B16 is 1.5 then the formula gives 3 that means the number (1.5)is in column M+3-1 that is in column O in the relevant row that is O16
if there is any doubt in my explanation post back.
If it is ok confirm "yes"
A B
1.5 m16:z16
2.2 m17:z17
3 m18:z18
4 m19:z19
5.5 m20:z20
7.5 M21:Z21
11 m22:z22
15 m23:z23
18.5 m24:z24
22 m25:z25
30 m26:z26
37 m27:z27
45 m28:z28
55 m29:z29
75 m30:z30
90 m31:z31
110 m32:z32
132 m33:z33
the data from column M to z from rows 16 to 33 IN SHEET1 FLLL up your data
now In sheet1 A1 or any other cell copy this formula
=MATCH(B16,INDIRECT(VLOOKUP(B16,Sheet2!$A$1:$B$18,2,FALSE)),0)
for e.g if B16 in sheet 1 is 1.50 then the formula above will give you the column no. counting from column M as 1,of entry 1.50 in M16:Z16
NOTE:
If the value in sheet 1 B16 is not available in the relevant row then the formula will given you %N/A that is not available
Note2
if the number in B16 is 1.5 then the formula gives 3 that means the number (1.5)is in column M+3-1 that is in column O in the relevant row that is O16
if there is any doubt in my explanation post back.
If it is ok confirm "yes"
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 7, 2009 at 06:30 AM
Dec 7, 2009 at 06:30 AM
sorry to note one more thing
in match function the third argument is -1,0 or 1.
0 finds the exact reference in this case B16 in the relevant reference row.
so I have given the third argument as 0 and not 1 as you have indcated.
in match function the third argument is -1,0 or 1.
0 finds the exact reference in this case B16 in the relevant reference row.
so I have given the third argument as 0 and not 1 as you have indcated.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 9, 2009 at 05:04 AM
Dec 9, 2009 at 05:04 AM
If you got correct answer it is ok
my formula is
=MATCH(B16,INDIRECT(VLOOKUP(B16,Sheet2!$A$1:$B$18,2,FALSE)),0)
where is 1 there.
my formula is
=MATCH(B16,INDIRECT(VLOOKUP(B16,Sheet2!$A$1:$B$18,2,FALSE)),0)
where is 1 there.
Dec 8, 2009 at 05:00 AM
thanks for your reply with effort but indirect function is not working it is coming zero when write the formula as you written.