Multiple Criteria INDEX and MATCH CSE Array Formula

Trailer Parts

New Member
As I searched for CSE Array Formula examples, I found few that were a complex as I sometimes need to find data in massive spreadsheets. So now that I finally figured out how to use these formulas, with help from a few on this forum, I wanted to post what I know and ask for guidance if I am off base or on track.

INDEX AND MATCH, the new VLOOKUP

If you have a value (our cost) to lookup on another sheet basedon a value (our part number), we all know that a VLOOKUP returns that datarather easily and quickly saving lots of time.

However, if you need to lookup up a value (our cost) on anysheet based on more than one value (our part number and a vendor partnumber), then INDEX and MATCH is a better “VLOOKUP” saving much frustration andvaluable time.

Let’s use the example below as our lookup values. This is Tab1.

 A​ B​ C​ 1 ​ ITEM NUMBER 2​ 8375545W-2B 3​ 345545SM-5C 4​ 345545WM-5C

<tbody>
</tbody>

TAB1

The chart below will be our lookup location of what we wantto ‘pull back’ to our value above. Thiswill be Tab2.

 A​ B​ C​ D​ 1​ ITEM NUMBER DESCRIPTION CATEGORY COST 2​ 8375545W-2B 8x3.75 545 WHL W/5.70-8 LRB TIRE WHEEL & TIRE ASSY \$10.25 3​ 560655WS-1C 15x6 545 PVDCRMOD W/ST205/75R LRC TIRE WHEEL & TIRE ASSY \$55.87 4​ 0856545B-3C SERIES 8 ALUM BLK MOD 545 W/ST205/75R LRC TIRE WHEEL & TIRE ASSY \$80.60 5​ 345545WM-5C 13x4 545 WM W/ST175/80 LRB TIRE WHEEL & TIRE ASSY \$25.31 6​ 345545SM-5C 13x4 545 SM W/ST175/80 LRB TIRE WHEEL & TIRE ASSY \$25.31 7​ 460545WS-5C 14X6 WS 545 W/ST205/75D LRC TIRE WHEEL & TIRE ASSY \$27.49 8​ 870545W-4C 8X7 545 WHT WHL W/18.5x8.50-8 LRC TIRE WHEEL & TIRE ASSY \$26.17

<tbody>
</tbody>

TAB2

Since our desired data is on Tab2, we will need to index it. The formula for indexing it is

=INDEX(\$A\$1:\$D\$7) Now is when it gets a bit tricky.If you just hit enter now, you won’t reallyget any result of value because index only gives you a place to look.The INDEX itself is almost useless if youstopped here.So really, you shouldn’thave typed anything yet.

But if we MATCH our lookup value (Tab1) to the index, thenwe get a result.

Before we type, let me explain the match. The MATCH will kind of be like a VLOOKUPexcept it’s called MATCH.=MATCH(A2,TAB2!\$A:\$D,0,4)The VLOOKUP for this would be =VLOOKUP(\$A2,TAB2!\$A:\$D,4,FALSE).The False in the vlookup is the 0 in theMATCH.It’s the MATCH TYPE, we use FALSEor 0 which means we want an EXACT MATCH.The 4 in the VLOOKUP is the column you want returned.The 4 in the MATCH is the column you want returned.Same concept, just a little reversed.Bill Gates keeping up on our toes, I suppose!

So, In Tab1, column B next to your Item Number (lookup value),you would type the formula for INDEX and MATCH.

Let me explain a couple of things. The ! in your formula means that it is a tabor page name ending.The \$ means thatyou want to keep that lookup in that row or column.You don’t want it to change.So if we put \$A2, it means that we want it tolook in A2 through A99999 but definitely staying in A.If you input \$A\$2, then it means that we wantit to look in “A2 only” for that lookup.If you only put A2 without the \$, then you’re basically saying lookanywhere for the lookup value.That iswhen you can really pull some data that just don’t add up (because it doesn’t –it is wrong most of the time).However, whenusing your lookup value, you’ll want to leave off the \$ or you’ll getjust one result for your entire spreadsheet.If \$A\$2 is your lookup value, then all results returned will be based onA2 only.

Now for inputting the INDEX / MATCH formula, in TAB1 (thechart you’re working to populate), next to your item number (lookup value) CellB2, type the following:

=INDEX(TAB2!\$A\$1:\$D\$7,MATCH(A2,TAB2!\$A\$1:\$A\$7,0),4)and hit enter.

Nothing happened, right? Well, you got an error.It isbecause the INDEX / MATCH is what is called an ARRAY FORMULA, or some call it aCSE FORMULA.It’s nickname is becauseyou have to Hold the Control and Shift Keys and Hit Enter (CSE = Control,Shift, Enter).The Curser must be in theACTION Field to make the CSE Array Formula to work.

But notice something else that I changed that if important(if you want a result other than error).

=INDEX(TAB2!\$A\$1:\$D\$7,MATCH(A2,TAB2!\$A\$1:\$A\$7,0),4)and hit enter.The INDEX formula remainsas it was looking at the entire “ARRAY” or tab that you want to find somethingin, but the ARRAY in the MATCH is only looking in the column that you want tofind the lookup value (item number).Iknow my item number is in column A of Tab2, so I have it look at TAB2!\$A\$1:\$A\$7.If my item number was in column B, then I wouldsimply change my match array to TAB2!\$B\$1:\$B\$7, even if my index was still A:D.You would change the lookup column of 4 to 3because if your look value is in column B, you’re now starting your lookup incolumn B so you want column 3 returned rather than 4, right?

Now you’re probably thinking, I could do this faster with aVLOOKUP. You’re right, you could andshould if it is that simple.But if you’rebeyond that simple search to a more complex one, then we need to change the formulasome.

 A​ B​ C​ D​ 1 ​ ITEM NUMBER VENDOR VENDOR NUMBER COST 2​ 8375545W-2B CURT 123456 3​ 345545SM-5C BUYERS 123654 4​ 345545WM-5C CURT 123585 5​ 345545WM-5C BUYERS 125223

<tbody>
</tbody>

TAB1

Notice that we have a duplicate lookup value (item number) becausethis item is potentially supplied by more than one vendor. In this case, fictitiously Curt and Buyers.We want to ensure that that cost we’repulling back to column C is accurately CURT and BUYERS, respectively.

 A ​ B​ C​ D​ E​ F​ 1 ​ ITEM NUMBER DESCRIPTION CATEGORY COST VENDOR VENDOR# 2​ 8375545W-2B 8x3.75 545 WHL W/5.70-8 LRB TIRE WHEEL & TIRE ASSY \$10.25 CURT 123456 3​ 560655WS-1C 15x6 545 PVDCRMOD W/ST205/75R LRC TIRE WHEEL & TIRE ASSY \$55.87 BUYERS 252125 4​ 0856545B-3C SERIES 8 ALUM BLK MOD 545 W/ST205/75R LRC TIRE WHEEL & TIRE ASSY \$80.60 BUYERS 325212 5​ 345545WM-5C 13x4 545 WM W/ST175/80 LRB TIRE WHEEL & TIRE ASSY \$25.31 CURT 123585 6​ 345545WM-5C 13x4 545 WM W/ST175/80 LRB TIRE WHEEL & TIRE ASSY \$20.89 BUYERS 125223 7​ 345545SM-5C 13x4 545 SM W/ST175/80 LRB TIRE WHEEL & TIRE ASSY \$25.31 BUYERS 123654 8​ 460545WS-5C 14X6 WS 545 W/ST205/75D LRC TIRE WHEEL & TIRE ASSY \$27.49 CURT 452112 9​ 870545W-4C 8X7 545 WHT WHL W/18.5x8.50-8 LRC TIRE WHEEL & TIRE ASSY \$26.17 BUYERS 854521

<tbody>
</tbody>

TAB2

So now, it seems to be more complicated. We want to use our lookup chart (Tab1), tocompare Column A to Tab2 Column A, but also not return a result unlessColumn C in Tab1 matches Column F in Tab2.

The INDEX FORMULA will change a bit because our INDEX ARRAYis larger. =INDEX(\$A\$1:\$F\$9)

The MATCH FOMULA changes some too because we have more thanone lookup.

When MATCH changes to more than one lookup, we have to movesome things around from the single lookup (again, I think just because BillGates is seeing how smart we are). NOTE:The * Asterisk is tellingthe formula “AND”.It says to do what isthe in parenthesis AND this one too, * AND this one too.

=INDEX(TAB2!\$A\$1:\$F\$7,MATCH(A2,TAB2!\$A\$1:\$A\$7,0),4) and hitenter.

=INDEX(TAB2!\$A\$1:\$F\$9,(1,(MATCH(TAB2!\$A\$1:\$A\$9=A2)*(TAB2!\$F\$1:\$F\$9=C2),0,4) and hit CSE.

Sometimes, you might have the same part number used for multiplevendors that for whatever reason also used the same part number, then you can justadd another ‘check’ into your formula.

So if you want it to check to ensure the vendor item matchesand the vendor, then you would just add another condition.

=INDEX(TAB2!\$A\$1:\$F\$9,(1,(MATCH(TAB2!\$A\$1:\$A\$9=A2)*(TAB2!\$F\$1:\$F\$9=C2)*(TAB2!\$E\$1:\$E\$9=B2),0,4) and hit CSE.The column you’re pulling is always based onyour INDEX…A.If your INDEX was J, thenyou would count columns from J.In thiscase you’re still pulling the 4th column, which is the cost.Good luck!

 ITEM NUMBER VENDOR VENDOR NUMBER COST 8375545W-2B CURT 123456 \$10.25 VLOOKUP 345545SM-5C BUYERS 123654 \$25.31 345545WM-5C CURT 123585 \$25.31 345545WM-5C BUYERS 125223 \$25.31 ITEM NUMBER VENDOR VENDOR NUMBER COST 8375545W-2B CURT 123456 \$10.25 INDEX / MATCH 345545SM-5C BUYERS 123654 \$25.31 345545WM-5C CURT 123585 \$25.31 345545WM-5C BUYERS 125223 \$25.31 Notice the difference between a one dimensional (whether INDEX / MATCH OR VLOOKUP) lookup and a multiple dimensional lookup? ITEM NUMBER VENDOR VENDOR NUMBER COST 8375545W-2B CURT 123456 \$10.25 INDEX / MATCH BASED ON MULTIPE CRITERIA 345545SM-5C BUYERS 123654 \$25.31 345545WM-5C CURT 123585 \$25.31 345545WM-5C BUYERS 125223 \$20.89 ITEM NUMBER VENDOR VENDOR NUMBER COST 8375545W-2B CURT 123456 \$10.25 INDEX / MATCH BASED ON MULTIPE CRITERIA 345545SM-5C BUYERS 123654 \$25.31 345545WM-5C CURT 123585 \$25.31 345545WM-5C BUYERS 125223 \$20.89

<tbody>
</tbody>

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Marcelo Branco

MrExcel MVP
=INDEX(TAB2!\$A\$1:\$D\$7,MATCH(A2,TAB2!\$A\$1:\$A\$7,0),4)and hit enter.

Nothing happened, right? Well, you got an error. It is because the INDEX / MATCH is what is called an ARRAY FORMULA, or some call it a CSE FORMULA.

Wrong! The formula works - it's a regular formula not an array formula (no need of CSE)

=INDEX(TAB2!\$A\$1:\$D\$7,MATCH(A2,TAB2!\$A\$1:\$A\$7,0),4)and hit enter.The INDEX formula remains as it was looking at the entire “ARRAY” or tab that you want to find somethingin, but the ARRAY in the MATCH is only looking in the column that you want to find the lookup value (item number).I know my item number is in column A of Tab2, so I have it look at TAB2!\$A\$1:\$A\$7.If my item number was in column B, then I would simply change my match array to TAB2!\$B\$1:\$B\$7, even if my index was still A:D. You would change the lookup column of 4 to 3 because if your look value is in column B, you’re now starting your lookup in column B so you want column 3 returned rather than 4, right?

Wrong! Using INDEX/MATCH you should keep the last parameter = 4 because you want the value in 4th column of the range A1:D7

On the other hand, using VLOOKUP(A2,TAB2!B1:D7,3,0) you should adjust the third parameter to 3.

M.

Last edited:

Replies
1
Views
192
Replies
5
Views
210
Replies
31
Views
2K
Replies
6
Views
695
Replies
3
Views
317

1,126,913
Messages
5,621,599
Members
415,847
Latest member
AlpinoHirsch

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.

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

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