Microsoft Office Forums Stuck on this formula!!!!
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
03-09-2006, 03:36 PM
 CADmanJP Novice Join Date: Mar 2006 Posts: 2
Stuck on this formula!!!!

Help!!!

I've tried everything but can't get the formula I need to do the following:

I have a table as follows

40 43 45 49
Group 1 10 20 30 40
Group 2 12 22 32 42
Group 3 14 24 34 44

On my spreadsheet I will manually input a Group number and also the value 40 (or 43,45,49). Now in another cell, B1 I want to create a formula that looks up which group I have typed in cell A1 e.g Group 2 and looks up which numeric value if I typed in cell A2 e.g. 43. I want the formula to return the answer 22 in cell B1.

Please please can anyone help. I have tried Index's, Match's, Arrays. I am 80% there but can't finish!!!!
#2
03-09-2006, 04:38 PM
 davesexcel Windows 7 32bit Office 2007 Novice Join Date: Mar 2006 Posts: 7

40 43 45 49
Group 1 10 20 30 40
Group 2 12 22 32 42
Group 3 14 24 34 44

Group 1 =MATCH(A6,A1:A4) 45 =HLOOKUP(C6,B1:E4,B6)

A6 has the dropdown menu
B6 has the Match formula
C6 has the drop down menu for the top row
D6 has the final result, the Hlookup formula

40 43 45 49
Group 1 10 20 30 40
Group 2 12 22 32 42
Group 3 14 24 34 44

Group 1 2 45 30 :P
#3
03-10-2006, 02:06 AM
 CADmanJP Novice Join Date: Mar 2006 Posts: 2

Thanks for your help, I don't think my explanation was clear enough.

However I found the answer from another post:

=Index(the table of values,match(group input,Group 1-3 column,0),match(numeric input,numeric row,0))

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post smoky Outlook 11 07-20-2010 02:11 PM Shahzad Excel 1 12-07-2008 04:13 AM frankalbo Outlook 1 09-10-2008 05:28 PM curlymal Excel 2 02-09-2007 11:34 AM mridley Mail Merge 0 07-27-2006 03:16 AM

Other Forums: Access Forums

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

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top