Looking up Data in Table

  • Thread starter Thread starter Jim Porter
  • Start date Start date
J

Jim Porter

I have an application where I will put data in a table
with 4 columns and 12 rows...this data will never change.

Then, I would like to have three cells for the user to
answer (Cell#1: A,P or G), (Cell#2: 2,3,4 or 5), Cell#3:
(C, M, D).

Based on the users response (like G, 3 & M), I would like
to display the contents (.96 in this case)of the data
table that corresponds to those choices in an answer cell.

Questions:
Material (A,G or P) ____
Category (2,3,4 or 5) ____
Conditions (C,M or D) ____

Answer: ____

- - C M D
A 2 .86 .82 .75
A 3 .79 .74 .68
A 4 .75 .61 .53
A 5 .79 .73 .66
G 2 .98 .96 .84
G 3 .98 .96 .84
G 4 .85 .75 .65
G 5 .90 .85 .75
P 2 .86 .82 .75
P 3 .79 .74 .68
P 4 .75 .61 .53
P 5 .79 .73 .66

How can this be done in Excel?
 
Select your table and give it a name, let's call it MyTable It would be a
5x12 large table (not 4 columns)
Then use this array formula

=INDEX(MyTable,MATCH(1,(INDEX(MyTable,,1)=H1)*(INDEX(MyTable,,2)=H2),0),MATC
H(H3,INDEX(MyTable,1,),0))

entered with ctrl + shift & enter

where H1 is your cell #1, H2 cell#2 and H3 cell#3
Using P in H1, 3 in H2 and D in H3 will return 0.68
 
The attached file shows one solution.
I reformatted the data to allow an array formula
{=SUM((I2=$A$2:$A$37)*(I3=$B$2:$B$37)*(I4=$C$2:$C$37)*D2:D37)}
to work.

If you need the data to remain in the table as explained in your OP,
please let me know.

Attachment filename: lookup exeltip.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=399175
 
Thanks Peo....I'll give that a try

Jim Porter

-----Original Message-----
Select your table and give it a name, let's call it MyTable It would be a
5x12 large table (not 4 columns)
Then use this array formula

=INDEX(MyTable,MATCH(1,(INDEX(MyTable,,1)=H1)*(INDEX (MyTable,,2)=H2),0),MATC
H(H3,INDEX(MyTable,1,),0))

entered with ctrl + shift & enter

where H1 is your cell #1, H2 cell#2 and H3 cell#3
Using P in H1, 3 in H2 and D in H3 will return 0.68

--

Regards,

Peo Sjoblom




.
Click to expand...
 
Kieran,

You are the Wizard!...Wow....works great...how can I ever
thank you enough...

Jim Porter
 
Back
Top