Lookup data based on data comparison

D

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
 
D

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?
 
P

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
 
D

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.
 
P

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
 
D

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top