This one will find the closest value greater than the lookup value in A2:A20
if there is no match and return the value from B2:B20

=INDEX(B2:B20,MATCH(SMALL(A2:A20,COUNTIF(A2:A20,"< ="&D1)+1),A2:A20,0))


--
Regards,

Peo Sjoblom



"Steve P." wrote in message
...
I need the next larger if theres not an exact match, I tried VLOOKUP and
would get the smaller value.
I'll play with the match formula.

I have another question, to take this one step further. I am currently
refering to only one sheet(table) for column footings based on the soil
bearing pressure. I would like to have tables for additional bearing
pressures in the workbook and by simply typing into a cell the bearing
pressure I need to use that it automatically uses the correct table?

"rdwj" wrote:

carefull with VLOOKUP, as it returns the first SMALLER value if there is
not
an exact match.
What you can use is the MATCH formula to find the position in the column
(use =match(value to lookup, range to lookup,1)+if(isna(match(value to
lookup, range to lookup)),0,1)
With the found value (which is the row in the range specified) you can
use
the offset formula to find the values you need: =offset(first cell in
range,
value found with the match formula, column offset if needed - can also be
set
with the reference cell)


"Toppers" wrote:

Steve,
VLOOKUP function is a possible candidate. Can you give an
example of the data (input/output) as this will help assist in
determining
what solution(s) is suitable.

"Steve P." wrote:

I'm new to Excel programming. I hope somebody can give me guidance
with this.
I work for an engineering firm and I regurally do calculations then
take the
result and refer to a chart in the back of a book and look in the
first
column for the first value larger than my result to get my design.
I have made a workbook where I can input my values and it does the
calculations. I have created a sheet with the table from the book.
What I
don't know how to do is make the first sheet analize the second sheet
using
the calculated result and reply the rest of that row.
If you can help me with this, tou would make my day, week, even my
month.
Thanks