Newbie he

I would like to select a column (A1:L1) based on the data in cell M1. My
data range is A2:L20. The data in A1:L1 starts w/1000 and steps up 500 where
B2=1500 and ends with L1=6500. M1 contains the value 1300. I would like the
formula to select the lower value column, "A1" and then LOOKUP and return a
value from the data range. Thanks for the formuls help, Dan

What would decide which row to return the data from out of 2 to 20?

Pete

On Nov 5, 3:29 pm, Dan wrote:
Newbie he

I would like to select a column (A1:L1) based on the data in cell M1. My
data range is A2:L20. The data in A1:L1 starts w/1000 and steps up 500 where
B2=1500 and ends with L1=6500. M1 contains the value 1300. I would like the
formula to select the lower value column, "A1" and then LOOKUP and return a
value from the data range. Thanks for the formuls help, Dan

Sorry out that. If A1 =1000 and B1 =1500 and my driver cell M1 =1300 then if
M1 was greater than A1 and less than B1 I would want to select the lower cell
A1. But.. how would I compare the M1 value to the range A1:L1?

"Pete_UK" wrote:

What would decide which row to return the data from out of 2 to 20?

Pete

On Nov 5, 3:29 pm, Dan wrote:
Newbie he

I would like to select a column (A1:L1) based on the data in cell M1. My
data range is A2:L20. The data in A1:L1 starts w/1000 and steps up 500 where
B2=1500 and ends with L1=6500. M1 contains the value 1300. I would like the
formula to select the lower value column, "A1" and then LOOKUP and return a
value from the data range. Thanks for the formuls help, Dan

I understand that you are using M1 to select the column - that can be
done with LOOKUP, or MATCH or even calculated as your values have
equal increments.

But, what are you using to select the row of data? Do you want all the
values in the selected column (say column A) to appear in M2:M20 ?

Pete

On Nov 5, 4:38 pm, Dan wrote:
Sorry out that. If A1 =1000 and B1 =1500 and my driver cell M1 =1300 then if
M1 was greater than A1 and less than B1 I would want to select the lower cell
A1. But.. how would I compare the M1 value to the range A1:L1?



"Pete_UK" wrote:
What would decide which row to return the data from out of 2 to 20?

Pete

On Nov 5, 3:29 pm, Dan wrote:
Newbie he

I would like to select a column (A1:L1) based on the data in cell M1. My
data range is A2:L20. The data in A1:L1 starts w/1000 and steps up 500 where
B2=1500 and ends with L1=6500. M1 contains the value 1300. I would like the
formula to select the lower value column, "A1" and then LOOKUP and return a
value from the data range. Thanks for the formuls help, Dan- Hide quoted text -

- Show quoted text -

Pete,

I just want to select one cell in the column. I believe that HLOOKUP will do
the job. The tough part for me is the first part.

"Pete_UK" wrote:

I understand that you are using M1 to select the column - that can be
done with LOOKUP, or MATCH or even calculated as your values have
equal increments.

But, what are you using to select the row of data? Do you want all the
values in the selected column (say column A) to appear in M2:M20 ?

Pete

On Nov 5, 4:38 pm, Dan wrote:
Sorry out that. If A1 =1000 and B1 =1500 and my driver cell M1 =1300 then if
M1 was greater than A1 and less than B1 I would want to select the lower cell
A1. But.. how would I compare the M1 value to the range A1:L1?



"Pete_UK" wrote:
What would decide which row to return the data from out of 2 to 20?

Pete

On Nov 5, 3:29 pm, Dan wrote:
Newbie he

I would like to select a column (A1:L1) based on the data in cell M1. My
data range is A2:L20. The data in A1:L1 starts w/1000 and steps up 500 where
B2=1500 and ends with L1=6500. M1 contains the value 1300. I would like the
formula to select the lower value column, "A1" and then LOOKUP and return a
value from the data range. Thanks for the formuls help, Dan- Hide quoted text -

- Show quoted text -

With the values 1000 through to 6500 in A1:L1 and M1 containing a
value like 1300, this formula will return the column number where the
lower matching value occurs:

=MATCH(M1,A1:L1)

and this slight variation will return the column letter:

=CHAR(MATCH(M1,A1:L1)+64)

There are many ways of achieving this, but is this what you want?

Hope this helps.

Pete


On Nov 5, 9:16 pm, Dan wrote:
Pete,

I just want to select one cell in the column. I believe that HLOOKUP will do
the job. The tough part for me is the first part.



"Pete_UK" wrote:
I understand that you are using M1 to select the column - that can be
done with LOOKUP, or MATCH or even calculated as your values have
equal increments.

But, what are you using to select the row of data? Do you want all the
values in the selected column (say column A) to appear in M2:M20 ?

Pete

On Nov 5, 4:38 pm, Dan wrote:
Sorry out that. If A1 =1000 and B1 =1500 and my driver cell M1 =1300 then if
M1 was greater than A1 and less than B1 I would want to select the lower cell
A1. But.. how would I compare the M1 value to the range A1:L1?

"Pete_UK" wrote:
What would decide which row to return the data from out of 2 to 20?

Pete

On Nov 5, 3:29 pm, Dan wrote:
Newbie he

I would like to select a column (A1:L1) based on the data in cell M1. My
data range is A2:L20. The data in A1:L1 starts w/1000 and steps up 500 where
B2=1500 and ends with L1=6500. M1 contains the value 1300. I would like the
formula to select the lower value column, "A1" and then LOOKUP and return a
value from the data range. Thanks for the formuls help, Dan- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -