|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension
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 |
#2
|
||||
|
||||
Please post a sample file showing expected results - Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Attached is an example file showing the data tables, inputs, outputs, a description of what I am trying to accomplish, and some examples.
G'day, Vinish |
#4
|
||||
|
||||
The attached seems to work , although I think it could be made simpler.
It uses array formulas to be committed with Ctrl+Shift+Enter
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Pecoflyer,
Thanks much. I agree that what you provided works. Array formulas are a bit out of my comfort zone although each time I get a complicated lookup question and ask it on a forum, the answer has always included an array formula. Perhaps I need to become better versed in these. I also agree that it can be simplified slightly as follows. Do you agree? You provided the key formula as being: {=INDEX(INDEX($B$8:$J$31,MATCH($C$1,$B$8:$B$31,0), ),,MATCH(TRUE,(INDEX($B$8:$J$31,MATCH($C$1,$B$8:$B $31,0),))>=$C$2,0))} I simplified this slightly to: =INDEX($B$8:$J$31,MATCH($C$1,$B$8:$B$31,0),MATCH(T RUE,(INDEX($B$8:$J$31,MATCH($C$1,$B$8:$B$31,0),))> =$C$2,0)) I was also surprised that the Offset function can have a blank for the second or third argument [e.g. Offset(B1..C5,28,)]. I figured the third argument in this example would have to be a 0. Of course, putting in 0 works but so does leaving it blank. Thanks again. G'day, Vinish |
#6
|
||||
|
||||
If your problem is answered, please mark the thread "solved" (under Thread Tools)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How To Maintain Fixed Dimension When Inserting Photo? | bruintriton | Word | 15 | 08-31-2015 07:10 PM |
Lookup table in word | ElfegoBaca | Word | 1 | 10-15-2014 06:34 PM |
Sorting algorithm for two dimensional array | NobodysPerfect | Word VBA | 1 | 04-07-2014 03:58 PM |
Slide dimension | Fallen | PowerPoint | 2 | 02-01-2013 01:49 PM |
Dimension Autoshapes | karthikcoep | Word | 0 | 01-16-2009 07:42 AM |