Excel challenge

[conphara]

Hi


I have a cell value (X) in one sheet (sheet X). That value (X) is in a matrix in another sheet (sheet Y) and is found in many rows (duplicates) but I am interested in the values from those rows. Normally I would use a vlookup but the challenge is this:

Every row in the matrix has a week number and several rows have the same week number. I have a certain week number in sheet X that has to match with the week number from the row that a vlookup would find.

Sheet X:
Value X
Week number 31

Sheet Y:
row 1: Week number 31. Value X. Value Y. Value Z.
row 2: Week number 32. Value X. Value Y. Value Z.

I want to return value Y is Value X is the same but only if the week numbers match.

 

[Aladin Akyurek]

Hi


I have a cell value (X) in one sheet (sheet X). That value (X) is in a matrix in another sheet (sheet Y) and is found in many rows (duplicates) but I am interested in the values from those rows. Normally I would use a vlookup but the challenge is this:

Every row in the matrix has a week number and several rows have the same week number. I have a certain week number in sheet X that has to match with the week number from the row that a vlookup would find.

Sheet X:
Value X
Week number 31

Sheet Y:
row 1: Week number 31. Value X. Value Y. Value Z.
row 2: Week number 32. Value X. Value Y. Value Z.

I want to return value Y is Value X is the same but only if the week numbers match.

Control+shift+enter, not just enter:

=INDEX(SheetY!Yrange,MATCH(1,IF(SheetY!WeekRange=WeekValue,IF(SheetY!Xrange=XValue,1)),0))

 

[conphara]

Just make sure my Excel challenge is perfectly clear.

Sheet1:
A B C D E
1 Week no.Inventory.Item no.Measure1.Measure2
2 31.8.1000.8,5.0,5
3 30.9.1000.7,5.0,6
4 31.10.2000.6,5.0,7
5 30.11.3000.6,9.0,8
6 31.22.2000.7,6.0,9
7 29.8.1000.5,9.0,4

Sheet2:
A B C
1 Week no.Item no.Measure1.Measure2.
2 31.1000."Formula"."Formula"


The matrix in Sheet1 is formatted as a table. I am not sure if that makes much of a difference.

Formula has to include the "measure1" (8,5) from sheet1 regarding the same item no. but only from the row with the same week no. from sheet2.

 

[Aladin Akyurek]

Just make sure my Excel challenge is perfectly clear.

Sheet1:
A B C D E
1 Week no. Inventory Item no. Measure1 Measure2
2 31 8 1000 8,5 0,5
3 30 9 1000 7,5 0,6
4 31 10 2000 6,5 0,7
5 30 11 3000 6,9 0,8
6 31 22 2000 7,6 0,9
7 29 8 1000 5,9 0,4

Sheet2:
A B C
1 31 Measure1 Measure2
2 1000 "Formula" "Formula"


The matrix in Sheet1 is formatted as a table. I am not sure if that makes much of a difference.

Formula has to include the "measure1" (8,5) from sheet1 regarding the same item no. but only from the row with the same week no. from sheet2.

Try to specify all of the condition values and the expected outcome.

 

[conphara]

The expected value for item no. 1000 is 8,5.

The condition values. Well, my measures should be found so it is not a question of true or false. It is only a question of finding the the right measure from the correct week. And that week is found in sheet1. The matrix is huge so much automation is received with a big smile.

 

[Aladin Akyurek]

The expected value for item no. 1000 is 8,5.

The condition values. Well, my measures should be found so it is not a question of true or false. It is only a question of finding the the right measure from the correct week. And that week is found in sheet1. The matrix is huge so much automation is received with a big smile.

Week is a condition (or look up value). What else?

 

[conphara]

Week no. is the only condition since item no. is the lookup value.

 

[Aladin Akyurek]

Week no. is the only condition since item no. is the lookup value.

Let Sheet1, A1:E7, house the sample you posted, including the headers.

Let Sheet2, A1:D2, house the processing that you need: Lookup values and headers included.

C2, control+shift+enter, not just enter, and copy across:

=INDEX(Sheet1!$D$2:$E$7,MATCH(1,IF(Sheet1!$A$2:$A$7=$A2,IF(Sheet1!$C$2:$C$7=$B2,1)),0),MATCH(C$1,Sheet1!$D$1:$E$1,0))

where A2 = 31, B2 = 100, C1 = Measure1, and D1 = Measure2.

 

[conphara]

I really appreciate the help and effort but I get an error in the first part of the formula highlighting "$E$7,MATCH" giving two options regarding index (matrix) and index (reference).

How should I handle this?

 

[Aladin Akyurek]

I really appreciate the help and effort but I get an error in the first part of the formula highlighting "$E$7,MATCH" giving two options regarding index (matrix) and index (reference).

How should I handle this?

Are you ona European system? If so...

=INDEX(Sheet1!$D$2:$E$7;MATCH(1;IF(Sheet1!$A$2:$A$7=$A2;IF(Sheet1!$C$2:$C$7=$B2;1));0);MATCH(C$1;Sheet1!$D$1:$E$1;0))

which must be confirmed with control+shift+enter, not just enter.