Cell range

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

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

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