#### Trailer Parts

##### New Member

- Joined
- Aug 25, 2018

- Messages
- 1

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 unless__Column 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=

**A**2)

*****(TAB2!$

**F**$1:$

**F**$9=

**C**2),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=

**A**2)

*****(TAB2!$

**F**$1:$

**F**$9=

**C**2)

*****(TAB2!$

**E**$1:$

**E**$9=

**B**2),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 4

^{th}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>