Well, it said a long title was preferred. Hope this one was okay.



In Excel 2010, I have a table with about 20 rows and about 10 columns. The values in each row are numerical and sorted ascending from left to right (4.5, 6.9, 11.6, 12.9, etc.).

There is a header row to the left of my data and this header row contains numerical data sorted ascending from top to bottom (4.5, 6.9, 11.6, 12.9, etc.).

If necessary, I could sort the table in descending order but would prefer not to.

I want to do the following:

1) Identify the row where the header column contains a specified value. This value will be an exact match with one of the entries in this column. This header column contains pipe sizes which have discreet values like, 0.5, 0.75, 1, 1.25, 1.5, 2, 3, 4, etc.

2) In the row identified in step #1, I want to find the smallest value that is greater than or equal to a specified value. This row contains circumferences of insulation that goes on the pipes. I need to find the smallest circumference that is greater than or equal to a reference value (the specified value). The data in each row is numerical, ascends from left to right, and can contain some blank cells at the left and/or right end of the row.

3) I want to return the value identified in step #2. If it is easier to return the reference to the cell (e.g. R15) containing the value identified in step #2, that would be fine also. I could then just use this reference in an equation.

I tried using hlookup and vlookup. The vlookup works since I am looking in the first column of the range but then I am stuck. I want to search only in the row containing the value found by the vlookup not in the whole table range based on a header row so hlookup doesn't work.

Perhaps some combination of match, index, and vlookup can work but I can't figure it out.

I hope that someone can help me with this. Thanks in advance.

G'day,

Vinish