Index match

[surkdidat]

Hopefully someone can help! I have the following formula, but I need to include it to look at the value in column BL for a specific value of "AAA"

=INDEX(Sheet1!$AN$2:$AN$33461,MATCH($A4,IF(Sheet1!$BJ$2:$BJ$33461=L$1,Sheet1!$B$2:$B$33461),0))/100

 

[AlanY]

Re: INDEX MATCH Help

may be

=INDEX(Sheet1!$AN$2:$AN$33461,MATCH($A4,IF(Sheet1!$BJ$2:$BJ$33461=L$1,IF(Sheet1!$BL$2:$BL$33461="AAA",Sheet1!$B$2:$B$33461)),0))/100

 

[surkdidat]

Re: INDEX MATCH Help

Thank you for this, however, it is not picking up the first value (which is also always the highest). How can I amend this so it will always pick up the highest matching crieteria ( a simple MAX( ) does not seem to work......
Would I somehow (and do not really understand it, need to use the AGGREGATE Formula?

may be

=INDEX(Sheet1!$AN$2:$AN$33461,MATCH($A4,IF(Sheet1!$BJ$2:$BJ$33461=L$1,IF(Sheet1!$BL$2:$BL$33461="AAA",Sheet1!$B$2:$B$33461)),0))/100

 

[AlanY]

Re: INDEX MATCH Help

could you post a small sample of your data and desired output here?

 

[surkdidat]

Re: INDEX MATCH Help

What may help is I have the following to find a minimum, but without the extra column lookup - can this be amended?

=AGGREGATE(15, 6, Sheet1!AB2:INDEX(Sheet1!AB:AB, MATCH(1E+99,Sheet1!AB:AB ))/((Sheet1!B2:INDEX(Sheet1!B:B, MATCH(1E+99,Sheet1!AB:AB ))=$A4)*(Sheet1!BJ2:INDEX(Sheet1!BJ:BJ, MATCH(1E+99,Sheet1!C:C ))=$M$1)), 1)/100

Alternatively, using the above:-

[COLOR=#333333][I]=INDEX(Sheet1!$AF$2:$AF$33461,MATCH($A4,IF(Sheet1!$BJ$2:$BJ$33461=L$1,IF(Sheet1!$BL$2:$BL$33461="AAA",Sheet1!$B$2:$B$33461)),0))/100[/I][/COLOR]

WOW No.|AF..|BJ.|BL...|
ROW 2.. |3.82|LEE|AAA|
ROW 3...|3.02|LEE|AAA|
ROW 4...|3.92|LEE|AAA|
ROW 5...|3.82|LEE|BBB|
ROW 6...|3.02|LEE|AAA|
ROW 7...|3.82|HAN|AAA|
ROW 8...|3.55|HAN|AAA|
ROW 9...|2.44|HAN|AAA|

So in the above it looked up column BJ for "LEE" and column BL for "AAA". I then want it to return the HIGHEST value (row 4 in this case of 3.92)

could you post a small sample of your data and desired output here?

 

[AlanY]

Re: INDEX MATCH Help

try this

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	WOW No.	AF..	BJ.	BL...		BJ.	BL...	AF..
2	ROW 2..	3.82	LEE	AAA		LEE	AAA	3.92
3	ROW 3...	3.02	LEE	AAA
4	ROW 4...	3.92	LEE	AAA
5	ROW 5...	3.82	LEE	BBB
6	ROW 6...	3.02	LEE	AAA
7	ROW 7...	3.82	HAN	AAA
8	ROW 8...	3.55	HAN	AAA
9	ROW 9...	2.44	HAN	AAA
Sheet8

Cell Formulas
Range	Formula
H2	=AGGREGATE(14,6,IF(C2:C9=F2,IF(D2:D9=G2,B2:B9)),1)

 

[surkdidat]

Re: INDEX MATCH Help

Hi Alan

Apologies, I forgot there is another lookup in the formula looking up column B on Sheet1 which is A, B, C, D, E, F etc...
I have tried to add it as an IF(AND....but this does not seem to work...

try this

Excel 2013/2016

	A	B	C	D	E	F	G	H
1	WOW No.	AF..	BJ.	BL...		BJ.	BL...	AF..
2	ROW 2..	3.82	LEE	AAA		LEE	AAA	3.92
3	ROW 3...	3.02	LEE	AAA
4	ROW 4...	3.92	LEE	AAA
5	ROW 5...	3.82	LEE	BBB
6	ROW 6...	3.02	LEE	AAA
7	ROW 7...	3.82	HAN	AAA
8	ROW 8...	3.55	HAN	AAA
9	ROW 9...	2.44	HAN	AAA

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula H2 =AGGREGATE(14,6,IF(C2:C9=F2,IF(D2:D9=G2,B2:B9)),1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

Re: INDEX MATCH Help

it's an array formula, enter without the {} and confirm with shift-control-enter

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	WOW No.	AF..	BJ.	BL...	Extra	BJ.	BL...		AF..
2	ROW 2..	3.82	LEE	AAA	A	LEE	AAA	A	3.82
3	ROW 3...	3.02	LEE	AAA	A
4	ROW 4...	3.92	LEE	AAA	B
5	ROW 5...	3.82	LEE	BBB	B
6	ROW 6...	3.02	LEE	AAA	B
7	ROW 7...	3.82	HAN	AAA	B
8	ROW 8...	3.55	HAN	AAA	B
9	ROW 9...	2.44	HAN	AAA	A
Sheet8

Cell Formulas
Range	Formula
I2	{=AGGREGATE(14,6,IF(C2:C9=F2,IF(D2:D9=G2,IF(E2:E9=H2,B2:B9))),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[surkdidat]

Re: INDEX MATCH Help

Many thanks, what is the Aggregate "code" for lowest value, just in case I need that in the future please? Can that be used in the same context as below?

it's an array formula, enter without the {} and confirm with shift-control-enter

Excel 2013/2016

	A	B	C	D	E	F	G	H	I
1	WOW No.	AF..	BJ.	BL...	Extra	BJ.	BL...		AF..
2	ROW 2..	3.82	LEE	AAA	A	LEE	AAA	A	3.82
3	ROW 3...	3.02	LEE	AAA	A
4	ROW 4...	3.92	LEE	AAA	B
5	ROW 5...	3.82	LEE	BBB	B
6	ROW 6...	3.02	LEE	AAA	B
7	ROW 7...	3.82	HAN	AAA	B
8	ROW 8...	3.55	HAN	AAA	B
9	ROW 9...	2.44	HAN	AAA	A

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula I2 {=AGGREGATE(14,6,IF(C2:C9=F2,IF(D2:D9=G2,IF(E2:E9=H2,B2:B9))),1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[AlanY]

Re: INDEX MATCH Help

the 15 in your original AGGREGATE() is for the SMALL() function, and 14 for LARGE()