Cell range

Closed
guna - Dec 6, 2009 at 11:27 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 9, 2009 at 05:04 AM
Hello,

i am using this formula in one place =MATCH(B16,M21:S21,1) but the range i have mentioned will varries based on the some other reference for example if B16 IS 1.50 THE RANGE IS M16:Z16 BUT THE B16 is 7.50 the range is M21:Z21. I have like this more then 10 variables. how to do this. pl. mail me


1.50 m16:z16
2.20 m17:z17
3.00 m18:z18
4.00 m19:z19
5.50 m20:z20
7.50 M21:Z21
11.00 m22:z22
15.00 m23:z23
18.50 m24:z24
22.00 m25:z25
30.00 m26:z26
37.00 m27:z27
45.00 m28:z28
55.00 m29:z29
75.00 m30:z30
90.00 m31:z31
110.00 m32:z32
132.00 m33:z33

3 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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

=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"
0
venkat,

thanks for your reply with effort but indirect function is not working it is coming zero when write the formula as you written.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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.
0