Excel Tips & Tricks ITT

Help Support Talkbeer:

Joined
Apr 10, 2013
Messages
9,087
Location
Squat Rack
I can confirm that INDEX MATCH defeats VLOOKUP in most situations. If they ever bitch about my internet activity, this topic is being used as evidence.
Not convinced that INDEX/MATCH meets my needs better than VLOOKUP.

I regularly have to match thousands of customer IDs across sheets. For example: I get a list of customers IDs on one sheet and need to match ID from another sheet that contains email address. VLOOKUP returns the email for each customer ID match.

From what I know about INDEX/MATCH, you can only input one value to search, is that correct?
 
Last edited:
Joined
Apr 10, 2013
Messages
9,087
Location
Squat Rack
Not convinced that INDEX/MATCH meets my needs better than VLOOKUP.

I regularly have to match thousands of customer IDs across sheets. For example: I get a list of customers IDs on one sheet and need to match ID from another sheet that contains email address. VLOOKUP returns the email for each customer ID match.

From what I know about INDEX/MATCH, you can only input one value to search, is that correct?
A little more research was needed. Got it.
 

BadJustin

Moderator
Staff member
Contributor
Joined
Oct 2, 2013
Messages
16,764
Location
Former Janitor
someone teach me how to make a fucking Pivot Table, I wanted to stab myself yesterday and stuck it on the backburner. Excel noob for sure. Fawk.
 
Joined
Dec 21, 2013
Messages
9,114
Location
LFK
Not convinced that INDEX/MATCH meets my needs better than VLOOKUP.

I regularly have to match thousands of customer IDs across sheets. For example: I get a list of customers IDs on one sheet and need to match ID from another sheet that contains email address. VLOOKUP returns the email for each customer ID match.

From what I know about INDEX/MATCH, you can only input one value to search, is that correct?
That's the one place I still think VLOOKUP wins - when you just need to append a single column to the end of your dataset. It's just simpler and faster to write.

I usually have to combine multiple columns from the table I'm "joining." So like all of the fields for a mailing address in separate columns. If you use the $ to lock the references correctly, you can just drag the formula around without needing to go back and change the retrieval column for each one.

The way I mentally envision it, instead of giving it a column number to retrieve, you're directly telling it which column with a cell reference (which is what allows it to be flexible with copying/dragging).
 
Joined
Dec 21, 2013
Messages
9,114
Location
LFK
someone teach me how to make a fucking Pivot Table, I wanted to stab myself yesterday and stuck it on the backburner. Excel noob for sure. Fawk.
I just highlight the whole thing and then go to to the Insert ribbon and choose the Pivot Table icon.

Then it gives you a list of fields on the right and you can drag them down to one of the four quadrants at the bottom (filters/columns/rows/values). From this point best thing to do is just mess with it and see what it does.
 
Joined
Apr 10, 2013
Messages
9,087
Location
Squat Rack
someone teach me how to make a fucking Pivot Table, I wanted to stab myself yesterday and stuck it on the backburner. Excel noob for sure. Fawk.
YouTube machine, bro. Srs. Your question is so incredibly vague, hard to answer it better than the nerds on 'Tube.
but its mostly because I know how to google.
So much this.
 
Joined
Apr 10, 2013
Messages
9,087
Location
Squat Rack
I just highlight the whole thing and then go to to the Insert ribbon and choose the Pivot Table icon.

Then it gives you a list of fields on the right and you can drag them down to one of the four quadrants at the bottom (filters/columns/rows/values). From this point best thing to do is just mess with it and see what it does.
Ctrl+Shift+End minimizes blanks by just selecting fields containing data.
 
Joined
Apr 10, 2013
Messages
9,087
Location
Squat Rack
That's the one place I still think VLOOKUP wins - when you just need to append a single column to the end of your dataset. It's just simpler and faster to write.

I usually have to combine multiple columns from the table I'm "joining." So like all of the fields for a mailing address in separate columns. If you use the $ to lock the references correctly, you can just drag the formula around without needing to go back and change the retrieval column for each one.

The way I mentally envision it, instead of giving it a column number to retrieve, you're directly telling it which column with a cell reference (which is what allows it to be flexible with copying/dragging).
I learned how to make it work for me and I actually like it more than VLOOKUP. It's hard not to just start entering the VLOOKUP syntax...
 
Joined
Apr 10, 2013
Messages
9,087
Location
Squat Rack
Keyboard shortcuts?



But seriously, I love you. I always deal with charts that have a bunch of holes in the data and I think I actually might use this one.
I get so pissed when there are blanks in my pivot and I have to filter them out.
Ctrl+Shift+Home if you're starting from somewhere within your data field and want to select all between current and home.
 
Joined
Apr 10, 2013
Messages
4,313
Location
The Misty Mountains
I get so pissed when there are blanks in my pivot and I have to filter them out.
Ctrl+Shift+Home if you're starting from somewhere within your data field and want to select all between current and home.
I inherited a contact list from my predecessor. Over 2,000 entries, mostly just emails but some with some additional random information. I have tried pairing it down into useful data and this will definitely be helpful.
 
Joined
Jul 24, 2013
Messages
7,678
Location
TalkBeer.com
I learned how to make it work for me and I actually like it more than VLOOKUP. It's hard not to just start entering the VLOOKUP syntax...
Once you do it enough, it becomes second nature just like VLOOKUP. It takes longer cause it's more complicated but I finally got there and now I can write index matches before I've even had one cup of coffee
 

Arbitrator

Not Patrick
Joined
Apr 10, 2013
Messages
11,930
How easy are Pivot Tables, vlookup, etc to pick up? Any good online tutorials?

Anyone here use R? I'm taking a course on it. Seems pretty similar to Matlab at a superficial level.
 
Joined
Apr 10, 2013
Messages
9,087
Location
Squat Rack
How easy are Pivot Tables, vlookup, etc to pick up? Any good online tutorials?
If I need to learn something, I always check out YouTube first. A quick search produced tons of results of pivot tables, but I haven't seen them so can't recommend.

Maybe I should make my own....??

I have no idea how to do that.
 
Joined
Jul 24, 2013
Messages
7,678
Location
TalkBeer.com
How easy are Pivot Tables, vlookup, etc to pick up? Any good online tutorials?

Anyone here use R? I'm taking a course on it. Seems pretty similar to Matlab at a superficial level.
Any one individual thing in Excel is pretty damn easy to learn, IMO, especially if you have any kind of technical familiarity at all. I don't have any particular online site i like better than others - if I'm trying to do something and it's not working or it's a new command to me, I just google it and there are usually a ton.

I use R on and off. Not really at all an expert yet. I'm really a Stata guy at heart, but I would very much like to be competent in R so that I can eventually all my analysis in a piece of software I can have at home for free rather than having to rely on the software my employer pays thousands of dollars a year for...
 
Joined
Jan 30, 2014
Messages
13,888
Location
Chicagoish
People who love INDEX MATCH over VLOOKUP, please explain when/how it is useful. I am having a helluva time working it into my excel routine.
 
Joined
Jul 24, 2013
Messages
7,678
Location
TalkBeer.com
People who love INDEX MATCH over VLOOKUP, please explain when/how it is useful. I am having a helluva time working it into my excel routine.
Yeah, I mean, VLOOKUP is fine, but besides the incredibly low bar of remembering the syntax of the command, there's literally no reason not to use INDEX MATCH, or, in other words, there's nothing you can do with VLOOKUP you can't do with INDEX MATCH. But there ARE a few big benefits:
  • Less likely to make an error in the column you're trying to pick, as you actually select the column rather than counting columns (people have totally destroyed entire analyses they did for me this way)
  • Especially in a very large spreadsheet (hundreds/thousands of records), the performance of INDEX MATCH is way superior - doesn't take much time at all to calculate as it doesn't have to run through the whole array
  • Your lookup column does not have to be to the left of the array you're trying to work with
 

Latest posts

Top