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

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

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

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

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

I would like to thank everybody for your help. I was able to piece together a
formula that works.

=IF((ISERROR(VLOOKUP(F16,'2000'!A1:D32,2,FALSE))), (OFFSET('2000'!A3,(MATCH(F16,'2000'!A3:A34,1)),1,1 ,1)),(VLOOKUP(F16,'2000'!A1:D32,2,FALSE)))

I used VLOOKUP with the FALSE option, which will return an error if a exact
match is not found, inside the ISERROR which will give a TRUE or FALSE
answer. That is the test for the IF function. If an exact match was not
found, I went with the MATCH/OFFSET method. If a exact match was found I just
used VLOOKUP

Now for my next question.
I have a sheets in the workbook named 1000, 2000, 3000 & 4000
the formula above refers to the 2000 sheet. At different time I need to
refer to the other sheets. Is there a way have a cell that I type in the name
of the sheet I need to refer to and the formula use that value to determine
which sheet to reference from?