I am working on a worksheet to lookup values from a table. I am having a problem figuring out the row and column dynamic lookup. I have two pieces of information. For example, 4 and $21,000. With these two pieces of information I need to be able to look across the top row to determine where 4 is, then look down that particular column and figure out the smallest value that is greater than or equal to $21,000. From this i need the percentage in the first column that this corresponds with. Using the table below the value I would be looking for would be 40%. I have tried a number of combinations using index, match, and offset but still have a problem writing a dynamic formula for determining the row (dollar value) after determining the correct column. This formula will need to be performed on multiple rows of data. Can anyone shed any light on what I might be missing![Confused :confused: :confused:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
<tbody>
</tbody><colgroup><col span="2"><col span="8"></colgroup>
The format of the table cant be changed as this will be used by others and the table will need to be updated periodically.
A | B | C | D | E | F | G | H | I | |
1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |
2 | 30% | $ 13,900 | $ 15,900 | $ 17,900 | $ 19,850 | $ 21,450 | $ 23,050 | $ 24,650 | $ 26,250 |
3 | 40% | $ 18,560 | $ 21,200 | $ 23,840 | $ 26,480 | $ 28,600 | $ 30,720 | $ 32,840 | $ 34,960 |
4 | 50% | $ 23,200 | $ 26,500 | $ 29,800 | $ 33,100 | $ 35,750 | $ 38,400 | $ 41,050 | $ 43,700 |
5 | 60% | $ 27,840 | $ 31,800 | $ 35,760 | $ 39,720 | $ 42,900 | $ 46,080 | $ 49,260 | $ 52,440 |
6 | 80% | $ 37,100 | $ 42,400 | $ 47,700 | $ 52,950 | $ 57,200 | $ 61,450 | $ 65,700 | $ 69,900 |
<tbody>
</tbody><colgroup><col span="2"><col span="8"></colgroup>
The format of the table cant be changed as this will be used by others and the table will need to be updated periodically.