Lookup data based on data comparison

[Dan]

Newbie here:

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

 

[Pete_UK]

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

Pete

 

[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]

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

 

[Dan]

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]

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

 

[Dan]

Pete,

I ended up using your advice to use LOOKUP for the initial row lookup and
then wrapped that output into an HLOOKUP to select the column data.

This is what worked: =HLOOKUP(LOOKUP(M1,A1:L1,A1:L1),A1:L20,3,FALSE)

Thanks for all the help. Dan

 

[Pete_UK]

Well, I'm glad you got something to work, so thanks for feeding back,
Dan. There are many ways of looking up data in a table.

Pete