Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-02-2015, 08:36 PM
Vinish Vinish is offline Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Windows 7 64bit Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Office 2010 64bit
Novice
Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension
 
Join Date: Dec 2015
Posts: 3
Vinish is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-03-2015, 01:08 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Windows 7 64bit Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 12-03-2015, 07:04 AM
Vinish Vinish is offline Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Windows 7 64bit Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Office 2010 64bit
Novice
Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension
 
Join Date: Dec 2015
Posts: 3
Vinish is on a distinguished road
Default

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
Attached Files
File Type: xlsx Example.xlsx (15.8 KB, 12 views)
Reply With Quote
  #4  
Old 12-03-2015, 07:59 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Windows 7 64bit Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

The attached seems to work , although I think it could be made simpler.

It uses array formulas to be committed with Ctrl+Shift+Enter
Attached Files
File Type: xlsx Copy of Example.xlsx (16.1 KB, 8 views)
__________________
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
Reply With Quote
  #5  
Old 12-03-2015, 09:54 AM
Vinish Vinish is offline Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Windows 7 64bit Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Office 2010 64bit
Novice
Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension
 
Join Date: Dec 2015
Posts: 3
Vinish is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 12-03-2015, 10:14 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Windows 7 64bit Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension How To Maintain Fixed Dimension When Inserting Photo? bruintriton Word 15 08-31-2015 07:10 PM
Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension Lookup table in word ElfegoBaca Word 1 10-15-2014 06:34 PM
Two dimensional table lookup with 2nd dimension occurring in Row found in first dimension 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft