Finding value in top row using multiple criteria

[mboi010]

Hello,

I am trying to find the top row value that matches the criteria from three left hand columns and a value in the table. There is a value, an annualized number value, that is used to find the value in the table. The value in the table is to be the closest less than value to that annualized value. Example below:


Specialty of store: Toys
Annualized number: 11

Column a Column b column c column d column e column f
10% 15% 20%
Bread Bakery Baker Head baker 8 10 12
Bread Bakery Baker Assistant 1 6 9 12
Cookie Shop Baker Head baker 4 8 12
Cookie Shop Baker Assistant 1 1 2 3

I am needing to find using the annualized number of "11", the closest less than value in the table using the criteria of "Bread bakery" "Baker" and "Assistant 1", this all needs to give me the percentage in the top row that it corresponds with. Is there a formula for this?

This example should find that "9" is the closest table value, and 15% is the top row number.

Thank you!

 

[mboi010]

The percentages are to be over the numbers. The format was not preserved when I posted it.

 

[Aladin Akyurek]

Care to repost the sample, using a hyphen as separator, like this

black swan-assistant 2-1-30-5

 

[mboi010]

Yes, No problem.

------------------------------------10% 15% 20%
Bread Bakery- Baker- Head baker- 8 - 10 - 12
Bread Bakery- Baker- Assistant - 6 - 9 - 12
Cookie Shop- Baker- Head baker- 4 - 8- 12
Cookie Shop- Baker- Assistant 1- 1 - 2 - 3

Thank you!

 

[Aladin Akyurek]

Thanks for the sample.

The goal is to identify the percentage value in the top row, which corresponds to a closest value which less than 11 while column A = bread bakery, column B = baker, and column C housing a value that contains the string assistant. That value is 9 which correlates with 15%.

Is this goal summary correct?

 

[mboi010]

Yes, this is the correct goal summary.

 

[Aladin Akyurek]

Yes, this is the correct goal summary.

What are the percentages (return values) for the following cases?

Case 1:

[TABLE="class: grid, width: 111"]
<tbody>[TR]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]cookie shop[/TD]
[/TR]
[TR]
[TD]baker[/TD]
[/TR]
[TR]
[TD]head baker[/TD]
[/TR]
</tbody>[/TABLE]

Case 2:

[TABLE="class: grid, width: 111"]
<tbody>[TR]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]cookie shop[/TD]
[/TR]
[TR]
[TD]baker[/TD]
[/TR]
[TR]
[TD]assistant[/TD]
[/TR]
</tbody>[/TABLE]

 

[mboi010]

Case 1: 10%
Case 2: 20%

Thank you

 

[Aladin Akyurek]

Case 1: 10%
Case 2: 20%

Thank you

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr][tr][td]

1​

[/td][td] [/td][td][/td][td][/td][td]

10%​

[/td][td]

15%​

[/td][td]

20%​

[/td][td]

11​

[/td][td]

7​

[/td][td]

7​

[/td][/tr]
[tr][td]

2​

[/td][td] Bread Bakery[/td][td]Baker[/td][td]Head baker[/td][td]

8​

[/td][td]

10​

[/td][td]

12​

[/td][td]bread bakery[/td][td]bread bakery[/td][td] cookie shop[/td][/tr]
[tr][td]

3​

[/td][td] Bread Bakery[/td][td]Baker[/td][td]Assistant[/td][td]

6​

[/td][td]

9​

[/td][td]

12​

[/td][td]baker[/td][td]baker[/td][td] baker[/td][/tr]
[tr][td]

4​

[/td][td] Cookie Shop[/td][td]Baker[/td][td]Head baker[/td][td]

4​

[/td][td]

8​

[/td][td]

12​

[/td][td]assistant[/td][td]assistant[/td][td] assistant[/td][/tr]
[tr][td]

5​

[/td][td] Cookie Shop[/td][td]Baker[/td][td]Assistant 1[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

0.15​

[/td][td]

0.1​

[/td][td]

0.2​

[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In I5 control+shift+enter, not just enter, and copy across:

=INDEX($D$1:$F$1,MIN(IF(($A$2:$A$5=I$2)*($B$2:$B$5=I$3)*ISNUMBER(SEARCH(I$4,$C$2:$C$5))*($D$2:$F$5<=I$1)*$D$2:$F$5=MAX(IF(($A$2:$A$5=I$2)*($B$2:$B$5=I$3)*ISNUMBER(SEARCH(I$4,$C$2:$C$5))*($D$2:$F$5<=I$1),$D$2:$F$5)),COLUMN($D$1:$F$1)-COLUMN($D$1)+1)))

 

[mboi010]

Wow, Thank you very much. This was a big help!