Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2004

Tip: Looking for answers? Try searching our database.

2 step LOOKUP?  Vector, array?  Help.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SCW - 06 Oct 2004 06:34 GMT
Need Help!

I've got a Data spreadsheet  looking like the following:

Start | End | Class |  1  |  2  |  3  |  ... thru to |  150 |
1  |  5  |  1  |  12.25  |  13.50  |  10.00  | ... assorted #s unde
above column headings |
7  |  7  |  2  |  9.85  |  15.00  |  11.20  | ... assorted #s unde
above column headings |
9  |  12 |  3  |  12.00  |  15.25  |  20.00  | ... assorted #s unde
above column headings |
and so on...

My goal is to first find a match contained between "Start" and "End
column values on this Data sheet (above) by entering a desired  numbe
(on different Master sheet).  This is relatively easy (usin
VLOOKUP)...
I.E.  
-  Example 1) Enter 4 on Master sheet.  On Data sheet - 4 is betwee
the Start value of 1 and End value of 5 - from the first row of dat
from above Data sheet.  Answer is Class 1.
-  Example 2)  Enter 12 on Master sheet.  On Data sheet - 12 i
included/between in the End value of row 3.  Answer is Class 3.

Also entered on the Master sheet is a desired number for columns heade
1 thru 150... based on this number and a number that is found during th
VLOOKUP (within the Start and End columns)... I want to extract th
corresponding number under the Cloumns headed 1 - 150.

I.E.
-  Since the first example above was a value of 4 found between 1 and
in Row 1 (Class 1), and if the desired column value of 3 is also given
the answer I want back is 10.00.
-  In the 2nd example, if 12 and 2 are given, I need to get back 15.25

And so on...

Can anyone enlighten me on how to pull this off?

Thanks  :
Frank Kabel - 06 Oct 2004 06:54 GMT
Hi
try
=INDEX('data'!A1:AX100,MATCH(4,'data'!A1:A100,1),MATCH(3,A1:AX1,0))

and replace 4 and 3 with some cell references

Signature

--
Regards
Frank Kabel
Frankfurt, Germany


> Need Help!
>
[quoted text clipped - 11 lines]
> My goal is to first find a match contained between "Start" and "End"
> column values on this Data sheet (above) by entering a desired
number
> (on different Master sheet).  This is relatively easy (using
> VLOOKUP)...
[quoted text clipped - 6 lines]
>
> Also entered on the Master sheet is a desired number for columns
headed
> 1 thru 150... based on this number and a number that is found during the
> VLOOKUP (within the Start and End columns)... I want to extract the
> corresponding number under the Cloumns headed 1 - 150.
>
> I.E.
> -  Since the first example above was a value of 4 found between 1 and
5
> in Row 1 (Class 1), and if the desired column value of 3 is also
given,
> the answer I want back is 10.00.
> -  In the 2nd example, if 12 and 2 are given, I need to get back 15.25
[quoted text clipped - 10 lines]
> SCW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1188
> View this thread: http://www.excelforum.com/showthread.php?threadid=266754
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.