Please help

I am looking for a formula that will give me the correct Result depending on
the Unit & Part I type into the cell

The result must pick the highest value with ref to the part and the unit used

If I use formula
=INDEX($G$24:$G$41,INDEX(MATCH($B$13&$B$15,$B$24:$ B$41&$C$24:$C$41,0),0))

Eg. If I select BBB with 10106378 the result should be "J" not "g"

If I use formula
{=VLOOKUP(MAX(IF(($B$27:$B$43=$B$15)*($C$27:$C$43= B16),$F$27:$F$43)),$F$27:$G$43,2,FALSE)}

Eg. If I select BBB with 10106195 the result should be "f" not "a" due to
the value of line 1 and 7 being the same

How do i combine these formulas into one

link data will be on another spead sheet
bbb ccc aaa
10106195
10106378
10117939
10099990
10117939


Lookup table
Unit Part Count Each Value Result
1 AAA 10106195 1 PC £91.60 a
2 AAA 10106378 8 PC £35.36 b
3 AAA 10117939 1 PC £0.01 c
4 AAA 10099990 2 PC £130.41 d
5 AAA 10117939 1 PC £1,000.00 e
6
7 BBB 10106195 2 PC £91.60 f
8 BBB 10106378 3 PC £35.36 g
9 BBB 10117939 1 PC £0.01 h
10 BBB 10099990 1 PC £130.41 i
11 BBB 10106378 5 PC £50.00 j
12
13 CCC 10106195 1 PC £91.60 k
14 CCC 10106378 1 PC £35.36 l
15 CCC 10117939 1 PC £0.01 m
16 CCC 10099990 1 PC £130.41 n
17 CCC 10106195 1 PC £100.00 o

Based on your data layout at the end, I think you want something like this:

=INDEX('Data Sheet'!F:F,SUMPRODUCT(--('Data Sheet'!A2:A100=B13),--('Data
Sheet'!A2:A100=B15),ROW(A2:A100)))

Formula finds the row number that corresponds with a "double data match" and
then plugs that into the INDEX function.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy" wrote:

Please help

I am looking for a formula that will give me the correct Result depending on
the Unit & Part I type into the cell

The result must pick the highest value with ref to the part and the unit used

If I use formula
=INDEX($G$24:$G$41,INDEX(MATCH($B$13&$B$15,$B$24:$ B$41&$C$24:$C$41,0),0))

Eg. If I select BBB with 10106378 the result should be "J" not "g"

If I use formula
{=VLOOKUP(MAX(IF(($B$27:$B$43=$B$15)*($C$27:$C$43= B16),$F$27:$F$43)),$F$27:$G$43,2,FALSE)}

Eg. If I select BBB with 10106195 the result should be "f" not "a" due to
the value of line 1 and 7 being the same

How do i combine these formulas into one

link data will be on another spead sheet
bbb ccc aaa
10106195
10106378
10117939
10099990
10117939


Lookup table
Unit Part Count Each Value Result
1 AAA 10106195 1 PC £91.60 a
2 AAA 10106378 8 PC £35.36 b
3 AAA 10117939 1 PC £0.01 c
4 AAA 10099990 2 PC £130.41 d
5 AAA 10117939 1 PC £1,000.00 e
6
7 BBB 10106195 2 PC £91.60 f
8 BBB 10106378 3 PC £35.36 g
9 BBB 10117939 1 PC £0.01 h
10 BBB 10099990 1 PC £130.41 i
11 BBB 10106378 5 PC £50.00 j
12
13 CCC 10106195 1 PC £91.60 k
14 CCC 10106378 1 PC £35.36 l
15 CCC 10117939 1 PC £0.01 m
16 CCC 10099990 1 PC £130.41 n
17 CCC 10106195 1 PC £100.00 o

Thank you Luke

With ref to your formula I miss typed my formula. sorry for the error

please review the new data.

If I use formula
=INDEX($F$30:$F$47,INDEX(MATCH($A$1&$A$2,$A$30:$A$ 47&$B$30:$B$47,0),0))

Eg. If I select BBB with 10106378 the result should be "J" not "g"

If I use formjla
{=VLOOKUP(MAX(IF(($A$30:$A$47=$A$1)*($B$27:$B$43=$ A$2),$E$30:$E$47)),$E$30:$F$47,2,FALSE)}

Eg. If I select BBB with 10106195 the result should be "f" not "a" due to
the value of line 1 and 7 being the same

A
1 bbb
2 10106195
3 10106378
4 10117939
5 10099990
6 10117939




A B C D E F
30 Unit Part Count Each Value Result
31 AAA 10106195 1 PC £91.60 a
32 AAA 10106378 8 PC £35.36 b
33 AAA 10117939 1 PC £0.01 c
34 AAA 10099990 2 PC £130.41 d
35 AAA 10117939 1 PC £1,000.00 e
36
37 BBB 10106195 2 PC £91.60 f
38 BBB 10106378 3 PC £35.36 g
39 BBB 10117939 1 PC £0.01 h
40 BBB 10099990 1 PC £130.41 i
41 BBB 10106378 5 PC £50.00 j
42
43 CCC 10106195 1 PC £91.60 k
44 CCC 10106378 1 PC £35.36 l
45 CCC 10117939 1 PC £0.01 m
46 CCC 10099990 1 PC £130.41 n
47 CCC 10106195 1 PC £100.00 o






"Luke M" wrote:

Based on your data layout at the end, I think you want something like this:

=INDEX('Data Sheet'!F:F,SUMPRODUCT(--('Data Sheet'!A2:A100=B13),--('Data
Sheet'!A2:A100=B15),ROW(A2:A100)))

Formula finds the row number that corresponds with a "double data match" and
then plugs that into the INDEX function.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy" wrote:

Please help

I am looking for a formula that will give me the correct Result depending on
the Unit & Part I type into the cell

The result must pick the highest value with ref to the part and the unit used

If I use formula
=INDEX($G$24:$G$41,INDEX(MATCH($B$13&$B$15,$B$24:$ B$41&$C$24:$C$41,0),0))

Eg. If I select BBB with 10106378 the result should be "J" not "g"

If I use formula
{=VLOOKUP(MAX(IF(($B$27:$B$43=$B$15)*($C$27:$C$43= B16),$F$27:$F$43)),$F$27:$G$43,2,FALSE)}

Eg. If I select BBB with 10106195 the result should be "f" not "a" due to
the value of line 1 and 7 being the same

How do i combine these formulas into one

link data will be on another spead sheet
bbb ccc aaa
10106195
10106378
10117939
10099990
10117939


Lookup table
Unit Part Count Each Value Result
1 AAA 10106195 1 PC £91.60 a
2 AAA 10106378 8 PC £35.36 b
3 AAA 10117939 1 PC £0.01 c
4 AAA 10099990 2 PC £130.41 d
5 AAA 10117939 1 PC £1,000.00 e
6
7 BBB 10106195 2 PC £91.60 f
8 BBB 10106378 3 PC £35.36 g
9 BBB 10117939 1 PC £0.01 h
10 BBB 10099990 1 PC £130.41 i
11 BBB 10106378 5 PC £50.00 j
12
13 CCC 10106195 1 PC £91.60 k
14 CCC 10106378 1 PC £35.36 l
15 CCC 10117939 1 PC £0.01 m
16 CCC 10099990 1 PC £130.41 n
17 CCC 10106195 1 PC £100.00 o

Ah. So, we first need to determine what the max price is within your initial
criteria, and then return the letter from column F that corresponds to a
match with that highest price and a match of your initial criteria.

You should be able to use this array** formula
=INDEX(F:F,MAX(IF((A31:A47=A1)*(B31:B47=B1)*(E31:E 47=MAX(IF((A31:A47=A1)*(B31:B47=B1),E31:E47))),ROW (F31:F47))))

**Note that array formulas must be confirmed using Ctrl+Shift+Enter, not
just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy" wrote:

Thank you Luke

With ref to your formula I miss typed my formula. sorry for the error

please review the new data.

If I use formula
=INDEX($F$30:$F$47,INDEX(MATCH($A$1&$A$2,$A$30:$A$ 47&$B$30:$B$47,0),0))

Eg. If I select BBB with 10106378 the result should be "J" not "g"

If I use formjla
{=VLOOKUP(MAX(IF(($A$30:$A$47=$A$1)*($B$27:$B$43=$ A$2),$E$30:$E$47)),$E$30:$F$47,2,FALSE)}

Eg. If I select BBB with 10106195 the result should be "f" not "a" due to
the value of line 1 and 7 being the same

A
1 bbb
2 10106195
3 10106378
4 10117939
5 10099990
6 10117939




A B C D E F
30 Unit Part Count Each Value Result
31 AAA 10106195 1 PC £91.60 a
32 AAA 10106378 8 PC £35.36 b
33 AAA 10117939 1 PC £0.01 c
34 AAA 10099990 2 PC £130.41 d
35 AAA 10117939 1 PC £1,000.00 e
36
37 BBB 10106195 2 PC £91.60 f
38 BBB 10106378 3 PC £35.36 g
39 BBB 10117939 1 PC £0.01 h
40 BBB 10099990 1 PC £130.41 i
41 BBB 10106378 5 PC £50.00 j
42
43 CCC 10106195 1 PC £91.60 k
44 CCC 10106378 1 PC £35.36 l
45 CCC 10117939 1 PC £0.01 m
46 CCC 10099990 1 PC £130.41 n
47 CCC 10106195 1 PC £100.00 o






"Luke M" wrote:

Based on your data layout at the end, I think you want something like this:

=INDEX('Data Sheet'!F:F,SUMPRODUCT(--('Data Sheet'!A2:A100=B13),--('Data
Sheet'!A2:A100=B15),ROW(A2:A100)))

Formula finds the row number that corresponds with a "double data match" and
then plugs that into the INDEX function.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy" wrote:

Please help

I am looking for a formula that will give me the correct Result depending on
the Unit & Part I type into the cell

The result must pick the highest value with ref to the part and the unit used

If I use formula
=INDEX($G$24:$G$41,INDEX(MATCH($B$13&$B$15,$B$24:$ B$41&$C$24:$C$41,0),0))

Eg. If I select BBB with 10106378 the result should be "J" not "g"

If I use formula
{=VLOOKUP(MAX(IF(($B$27:$B$43=$B$15)*($C$27:$C$43= B16),$F$27:$F$43)),$F$27:$G$43,2,FALSE)}

Eg. If I select BBB with 10106195 the result should be "f" not "a" due to
the value of line 1 and 7 being the same

How do i combine these formulas into one

link data will be on another spead sheet
bbb ccc aaa
10106195
10106378
10117939
10099990
10117939


Lookup table
Unit Part Count Each Value Result
1 AAA 10106195 1 PC £91.60 a
2 AAA 10106378 8 PC £35.36 b
3 AAA 10117939 1 PC £0.01 c
4 AAA 10099990 2 PC £130.41 d
5 AAA 10117939 1 PC £1,000.00 e
6
7 BBB 10106195 2 PC £91.60 f
8 BBB 10106378 3 PC £35.36 g
9 BBB 10117939 1 PC £0.01 h
10 BBB 10099990 1 PC £130.41 i
11 BBB 10106378 5 PC £50.00 j
12
13 CCC 10106195 1 PC £91.60 k
14 CCC 10106378 1 PC £35.36 l
15 CCC 10117939 1 PC £0.01 m
16 CCC 10099990 1 PC £130.41 n
17 CCC 10106195 1 PC £100.00 o

Thank you solving my formula

Andy

"Luke M" wrote:

Ah. So, we first need to determine what the max price is within your initial
criteria, and then return the letter from column F that corresponds to a
match with that highest price and a match of your initial criteria.

You should be able to use this array** formula
=INDEX(F:F,MAX(IF((A31:A47=A1)*(B31:B47=B1)*(E31:E 47=MAX(IF((A31:A47=A1)*(B31:B47=B1),E31:E47))),ROW (F31:F47))))

**Note that array formulas must be confirmed using Ctrl+Shift+Enter, not
just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy" wrote:

Thank you Luke

With ref to your formula I miss typed my formula. sorry for the error

please review the new data.

If I use formula
=INDEX($F$30:$F$47,INDEX(MATCH($A$1&$A$2,$A$30:$A$ 47&$B$30:$B$47,0),0))

Eg. If I select BBB with 10106378 the result should be "J" not "g"

If I use formjla
{=VLOOKUP(MAX(IF(($A$30:$A$47=$A$1)*($B$27:$B$43=$ A$2),$E$30:$E$47)),$E$30:$F$47,2,FALSE)}

Eg. If I select BBB with 10106195 the result should be "f" not "a" due to
the value of line 1 and 7 being the same

A
1 bbb
2 10106195
3 10106378
4 10117939
5 10099990
6 10117939




A B C D E F
30 Unit Part Count Each Value Result
31 AAA 10106195 1 PC £91.60 a
32 AAA 10106378 8 PC £35.36 b
33 AAA 10117939 1 PC £0.01 c
34 AAA 10099990 2 PC £130.41 d
35 AAA 10117939 1 PC £1,000.00 e
36
37 BBB 10106195 2 PC £91.60 f
38 BBB 10106378 3 PC £35.36 g
39 BBB 10117939 1 PC £0.01 h
40 BBB 10099990 1 PC £130.41 i
41 BBB 10106378 5 PC £50.00 j
42
43 CCC 10106195 1 PC £91.60 k
44 CCC 10106378 1 PC £35.36 l
45 CCC 10117939 1 PC £0.01 m
46 CCC 10099990 1 PC £130.41 n
47 CCC 10106195 1 PC £100.00 o






"Luke M" wrote:

Based on your data layout at the end, I think you want something like this:

=INDEX('Data Sheet'!F:F,SUMPRODUCT(--('Data Sheet'!A2:A100=B13),--('Data
Sheet'!A2:A100=B15),ROW(A2:A100)))

Formula finds the row number that corresponds with a "double data match" and
then plugs that into the INDEX function.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy" wrote:

Please help

I am looking for a formula that will give me the correct Result depending on
the Unit & Part I type into the cell

The result must pick the highest value with ref to the part and the unit used

If I use formula
=INDEX($G$24:$G$41,INDEX(MATCH($B$13&$B$15,$B$24:$ B$41&$C$24:$C$41,0),0))

Eg. If I select BBB with 10106378 the result should be "J" not "g"

If I use formula
{=VLOOKUP(MAX(IF(($B$27:$B$43=$B$15)*($C$27:$C$43= B16),$F$27:$F$43)),$F$27:$G$43,2,FALSE)}

Eg. If I select BBB with 10106195 the result should be "f" not "a" due to
the value of line 1 and 7 being the same

How do i combine these formulas into one

link data will be on another spead sheet
bbb ccc aaa
10106195
10106378
10117939
10099990
10117939


Lookup table
Unit Part Count Each Value Result
1 AAA 10106195 1 PC £91.60 a
2 AAA 10106378 8 PC £35.36 b
3 AAA 10117939 1 PC £0.01 c
4 AAA 10099990 2 PC £130.41 d
5 AAA 10117939 1 PC £1,000.00 e
6
7 BBB 10106195 2 PC £91.60 f
8 BBB 10106378 3 PC £35.36 g
9 BBB 10117939 1 PC £0.01 h
10 BBB 10099990 1 PC £130.41 i
11 BBB 10106378 5 PC £50.00 j
12
13 CCC 10106195 1 PC £91.60 k
14 CCC 10106378 1 PC £35.36 l
15 CCC 10117939 1 PC £0.01 m
16 CCC 10099990 1 PC £130.41 n
17 CCC 10106195 1 PC £100.00 o