Trailer Parts
New Member
- Joined
- Aug 25, 2018
- Messages
- 1
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.
<tbody>
</tbody>
TAB1
The chart below will be our lookup location of what we wantto ‘pull back’ to our value above. Thiswill be Tab2.
<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.
<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.
<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!
<tbody>
</tbody>
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>
