if / match

[Guest]

In spreadsheet 1, have a spreadsheet which has people’s names, job numbers
and the number of hours they worked on various jobs.

Spreadsheet 1
Col A Col B Col C
Row1. Job# 1 Job #2
Row2 Mr A.. 54hr 21hr etc
Etc

Spreadsheet 2
Row 1ColA (col b)Mr A. Mr B. Mr. C
Job#1 **
Job#2
etc

** (I want a function that will look for the job# and the person's name in
spreadhseet 1 and if the name matches and the hrs for that Job>3 in
spreadsheet 1, then put the person’s name (e.g B1 Mr A.) in this cell in
spreadsheet 2.

What is the best way to do this? Thanks!

 

[Guest]

Does my solutin works:

Sheet1.
B C D E F G
2 Worker Job#1 Job#2 Job#3 Job#4 Job#5
3 Sean 25 hr 32 hr 65 hr 58 hr 12 hr
4 Joe 50 hr 15 hr 35 hr 45 hr 45 hr
5 Johan 30 hr 20 hr 24 hr 65 hr 14 hr
6 Steve 40 hr 45 hr 15 hr 45 hr 47 hr
7 William 15 hr 25 hr 45 hr 14 hr 41 hr

Sheet2
B C D E F G
2 Job Joe Johan Sean Steve William
3 Job#1 50 hr 30 hr 25 hr 40 hr 15 hr
4 Job#2 15 hr 20 hr 32 hr 45 hr 25 hr
5 Job#3 35 hr 24 hr 65 hr 15 hr 45 hr
6 Job#4 45 hr 65 hr 58 hr 45 hr 14 hr
7 Job#5 45 hr 14 hr 12 hr 47 hr 41 hr

Formula in Sheet2 cell C3:
=INDEX(Sheet1!$C$3:$G$7;MATCH(C$2;Sheet1!$B$3:$B$7;0);MATCH($B3;Sheet1!$C$2:$G$2;0))
Formula in Sheet2 cell G7:
=INDEX(Sheet1!$C$3:$G$7;MATCH(G$2;Sheet1!$B$3:$B$7;0);MATCH($B7;Sheet1!$C$2:$G$2;0))

 

[Guest]

Aivis thanks. I got an error on the formula. I also did not explain well what
I am trying to do let me try again.
Input sheet
B C D E F G
2 worker 1245 1258 1259 2199 2560
3 sean 25 32 12
4 joe 50 45
5 johan 5 45 65 14
6 steve 40 1 45
7 william 15 25 14 41
------------------------------------------------------------------------------------
Output sheet
9 Sean Steve
10 1243
11 1245 Sean Steve
12 1258
13 1259 Sean (not show Steve because< 3hrs)
14 2187
15 2199 Steve
ETC

For example in c11, the formula should match C9 and B11 to the input sheet.
If it finds a value that is greater than 3 hrs where those values intesect,
it should put the value of c9 (Sean, in this case) in cell c11. If not then
put nothing "".

Thanks.

 

[Guest]

How about this?:

Formula in C11: =IF(ISNA(HLOOKUP($B11;$C$2:$G$2;1;0));"
";IF(INDEX($C$3:$G$7;MATCH(C$10;$B$3:$B$7;0);MATCH($B11;$C$2:$G$2;0))<4;"
";C$10))
Formula in G17: =IF(ISNA(HLOOKUP($B17;$C$2:$G$2;1;0));"
";IF(INDEX($C$3:$G$7;MATCH(G$10;$B$3:$B$7;0);MATCH($B17;$C$2:$G$2;0))<4;"
";G$10))

1 C D E F G
2 1245 1258 1259 2199 2560
3 sean 25 32 12
4 joe 50 45
5 johan 5 45 65 14
6 steve 40 1 45
7 william 15 25 14 41
8
9 C D E F G
10 sean joe johan steve william
11 1243
12 1245 sean joe steve william
13 1258 johan
14 1259 sean johan william
15 2187
16 2199 joe johan steve william
17 2560 sean johan william