Index match

S

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
594
Office Version
  1. 365
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"

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

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
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?

AlanY said:
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
Click to expand...
 
Upvote 0
Re: INDEX MATCH Help

could you post a small sample of your data and desired output here?
 
Upvote 0
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?

Code: 
=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:-

Code: 
[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)

AlanY said:
could you post a small sample of your data and desired output here?
Click to expand...
 
Upvote 0
Re: INDEX MATCH Help

try this


Excel 2013/2016
ABCDEFGH
1WOW No.AF..BJ.BL...BJ.BL...AF..
2ROW 2..3.82LEEAAALEEAAA3.92
3ROW 3...3.02LEEAAA
4ROW 4...3.92LEEAAA
5ROW 5...3.82LEEBBB
6ROW 6...3.02LEEAAA
7ROW 7...3.82HANAAA
8ROW 8...3.55HANAAA
9ROW 9...2.44HANAAA
Sheet8
Cell Formulas
RangeFormula
H2=AGGREGATE(14,6,IF(C2:C9=F2,IF(D2:D9=G2,B2:B9)),1)
 
Upvote 0
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...
AlanY said:
try this

Excel 2013/2016
ABCDEFGH
WOW No.AF..BJ.BL...BJ.BL...AF..
ROW 2.. LEEAAALEEAAA
ROW 3...LEEAAA
ROW 4...LEEAAA
ROW 5...LEEBBB
ROW 6...LEEAAA
ROW 7...HANAAA
ROW 8...HANAAA
ROW 9...HANAAA

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3.82[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3.92[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3.02[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3.92[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3.82[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3.02[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3.82[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3.55[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2.44[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=AGGREGATE(14,6,IF(C2:C9=F2,IF(D2:D9=G2,B2:B9)),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Re: INDEX MATCH Help

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


Excel 2013/2016
ABCDEFGHI
1WOW No.AF..BJ.BL...ExtraBJ.BL...AF..
2ROW 2..3.82LEEAAAALEEAAAA3.82
3ROW 3...3.02LEEAAAA
4ROW 4...3.92LEEAAAB
5ROW 5...3.82LEEBBBB
6ROW 6...3.02LEEAAAB
7ROW 7...3.82HANAAAB
8ROW 8...3.55HANAAAB
9ROW 9...2.44HANAAAA
Sheet8
Cell Formulas
RangeFormula
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.
 
Upvote 0
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?

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

Excel 2013/2016
ABCDEFGHI
WOW No.AF..BJ.BL...ExtraBJ.BL...AF..
ROW 2.. LEEAAAALEEAAAA
ROW 3...LEEAAAA
ROW 4...LEEAAAB
ROW 5...LEEBBBB
ROW 6...LEEAAAB
ROW 7...HANAAAB
ROW 8...HANAAAB
ROW 9...HANAAAA

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3.82[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3.82[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3.02[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3.92[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3.82[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3.02[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3.82[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3.55[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2.44[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=AGGREGATE(14,6,IF(C2:C9=F2,IF(D2:D9=G2,IF(E2:E9=H2,B2:B9))),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Re: INDEX MATCH Help

the 15 in your original AGGREGATE() is for the SMALL() function, and 14 for LARGE()
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
405
Alex Blakenburg
A
D
  • Solved
Exact Match with ISNUMBER(SEARCH)
Replies
2
Views
221
danhenshy23
D
BrettOlbrys1
  • Solved
How to Use SortBy function?
Replies
4
Views
226
RoryA
RoryA
J
  • Solved
Assistance with Index Match formula
Replies
2
Views
202
Jay88
J
T
  • Question Question
Match specific rows with a column
Replies
1
Views
176
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top