Formula/Function to find Cell information

[Monni70]

Please somebody help me this complex problem.
I have two separate sheets - sheet no. 1 and sheet no. 2. (Please se
the attached .jpg file)
The sheet no. 1 is filled manually i.e. Cell information is written b
user.
The problem is sheet No. 2, which should have the Cells included som
sort of automatic "find" function to search correct Cell informatio
from sheet no. 1.
The Cells B5 to D7 in sheet no. 2 should have some sort of find/seac
function or formula to find correct information from the Cells B3...G
in sheet no. 1. When the correct information is find the return valu
should be the one of the value from the Column A in sheet no. 1 (eithe
"car1" or "car2" or "car3" on the .jpg file).
I.e. the Cell B5 tries to search if it finds "Yellow Car" for Week-1.

Hopely I expressed this problem enough clearly....thanks in advance fo
any help! :confused

Attachment filename: sheet no1 and no2.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=55660

 

[Frank Kabel]

Hi
enter the following in B5 as array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX('sheet1'!$A$3:$A$5,MATCH(1,('sheet1'!$B$3:$B$5=$A5)*('sheet1'!$C
$3:$C$5=A$3),0))

in B6 enter
=INDEX('sheet1'!$A$3:$A$5,MATCH(1,('sheet1'!$D$3:$D$5=$A5)*('sheet1'!$E
$3:$E$5=A$3),0))

B7:
=INDEX('sheet1'!$A$3:$A$5,MATCH(1,('sheet1'!$F$3:$F$5=$A5)*('sheet1'!$G
$3:$G$5=A$3),0))

copy these formulas to the right (all array entered)