Hello everyone,
I am working on a workbook where I need to look up a value based on a range of values. I need a formula (not a macro) to locate a parameter based on another parameter that must fall within a bracket/limit. So, I will need Excel to compare two values in a table to determine which row it should be looking at, and then return a value.
Example:
<TBODY>
</TBODY>
Column B should be automatically determined with the formula by referencing the lookup table below.
<TBODY>
</TBODY>
I have been able to make it work by using:
That gets quite cumbersome though as the lookup table grows. I am hoping there is a better way.<E2),F2,IF... grows.="" table="" lookup="" the="" as="" gets="" cumbersome="" quite="" is="" that="" better="" a="" there="" hoping="" am="" i="" though="" way.
Sorry for the quotes around the carrots. HTML didn't like it without them...
Thanks!
David</E2),F2,IF...>
I am working on a workbook where I need to look up a value based on a range of values. I need a formula (not a macro) to locate a parameter based on another parameter that must fall within a bracket/limit. So, I will need Excel to compare two values in a table to determine which row it should be looking at, and then return a value.
Example:
| A | B | |
| 1 | Beam Length | Thickness |
| 2 | 12 | 1.5 |
| 3 | 7 | 1 |
<TBODY>
</TBODY>
Column B should be automatically determined with the formula by referencing the lookup table below.
| D | E | F | |
| 1 | MIN. LENGTH | MAX. LENGTH | THICKNESS |
| 2 | 0.0 | 5.99 | 0.5 |
| 3 | 6 | 9.99 | 1 |
| 4 | 10 | 13.99 | 1.5 |
| 5 | 14 | 20 | 2 |
<TBODY>
</TBODY>
I have been able to make it work by using:
Code:
=if(and(A2">"D2,A2"<"E2,F2,if(...<E2),F2,IF(...[COLOR=#222222][FONT=Verdana]<E2),F2,IF(... span <>[/FONT][/COLOR]Sorry for the quotes around the carrots. HTML didn't like it without them...
Thanks!
David</E2),F2,IF...>
