Do you vlookup?

Pelvis

This one is aimed at the office workers.

Do you know how to vlookup? If you work with excel on a daily basis, shouldn't you know this very simple function?

I'm sick of getting emails from people who have worked with spreadsheets for years asking me to do this stuff, people who I would have thought were reasonably tech savvy.

What gives?? Are they lazy or stupid?

amcalester

Vlookup is so last year, it's all about INDEX MATCH now.

Vlookup hlookup index match, but my favourite is still sumproduct(--(lookup column) (lookup row) (sumrange))
Not to be confused with normal sumproduct, it just does a vlookup or hlookup or both and sums the results instead of only returning the first result. Has to be the handiest

baylah17

I don't know what a Tracker Mortgage is!

frag420

I can pivet...

sbsquarepants

Vlookup sounds like a dating app:)

s3rtvdbwfj81ch

yeah, vlookup, hlookup, index match, pivot tables

my boss currently loves me because I can add a second vertical axis on charts

LithiumKid1976

pelvis, i feel you pain, i work in IT, users dont want to learn, they just want the answers!

the newer versions of excel literally tell you what you need to do, but even then they wont get it right.

my personal favourite is =sumif

Mrs OBumble

Kassidy Gray Smog wrote: »

yeah, vlookup, hlookup, index match, pivot tables

my boss currently loves me because I can add a second vertical axis on charts

It's all so very 1990s.

Yes, seriously. I was doing that stuff in Excel in the last century, before some of you were even born.

It's OLD.

Seems to me there are more people who don't know how to do that stuff in Excel / Sheets now than ever before. Youngsters who didn't get to watch the office-productivity tools grow, eagerly awaiting each new release to see what new things were possible. They know how to do simple formulas, but never bothered learning anything beyond that.

pickarooney

Do you guys even inner join?

x43r0

If you don't test your results for significance then don't even talk to me

Are Am Eye

Vlot?

Princess Consuela Bananahammock

this sounds like it should have a forum of its own...

cantdecide

Mrs OBumble wrote: »

Seems to me there are more people who don't know how to do that stuff in Excel / Sheets now than ever before. Youngsters who didn't get to watch the office-productivity tools grow, eagerly awaiting each new release to see what new things were possible. They know how to do simple formulas, but never bothered learning anything beyond that.

It's gotten too complex to grasp to the point of being productive without a real world context and lots of practice through examples. Tuition in an environment where no real harm can be done is what's needed, IMO with a huge cost in time and fumbling. As someone who spent most of the noughties doing manual labour and not 'growing up' with it, I'm seriously disadvantaged now. Once I have my degree finished next year, I'm going to have to circle back and see what's out there to help in terms of tuition.

diomed

countif for me, preferably in conditional formatting.

Shenshen

vlookup, pivot, macros and for the last couple of months google scripts. For building spreadsheet templates, my favourite on is still the simple IF.

And yes, I get your frustration. Spreadsheet programs (Excel, LibreOffice, Google sheet) all hold an enormous number of ways to facilitate your work, via formulas, formatting and macros.
And most users never get past "Sum". Some don't even get that far. I sometimes feel sorry for the clever people who developed these programs.

pickarooney

pickarooney wrote: »

Do you guys even inner join?

Select * from AH where poster not in (select distinct poster from AH where not inner join)
Lols

pumpkin4life

Do you lads even Panda?

Colonel Claptrap

How to ruin a perfectly good morning: hit F1 instead of F2.

murpho999

Seriously, if people can learn to do sumif, pivots, and vlookups then some job interviewers will consider them to be geniuses!

pickarooney

It's all fun and games until you move abroad and all the formulas have different names.

****. That. Noise.

ILikeBoats

INDEX MATCH Master Race

Antar Bolaeisk

I try to steer people towards INDEX MATCH or INDIRECT MATCH however once you start explaining about the difference between absolute and relative cell referencing eyes become glazed.

Once I roll out array formula they treat me like some form of magician.

Really first and foremost they need to worry about how they're structuring their data and worrying about the formulas later but they tend to show up with some pile of disjointed, unorganised pile of information that they somehow think a vlookup will fix for them...

My favourite though are the invisible boxes people manage to somehow create on spreadsheets. I've never been able to figure out where they come from but they're a pain to remove.

Ficheall

People still use Excel? :pac:

sbsquarepants

sbsquarepants wrote: »

Vlookup sounds like a dating app:)

V for voyeur

Ipso

Array formulae.
Great youtube channel for learnin excel.
https://www.youtube.com/channel/UCkndrGoNpUDV-uia6a9jwVg

whiskeyman

You guys should hold a party....


In a function room!

Permabear

This post has been deleted.

Jimbob1977

A recruitment agent once suggested that Pivot Tables and Vlookups were 'Advanced Excel'.

I can only assume the hiring company was run by Arts students or chimpanzees.

Northern Monkey

I'm rather partial to sumifs at the moment.

brevity

Anyone remember that secret game that was in older versions of Excel?

I think it was a racing game.

Ipso

Jimbob1977 wrote: »

A recruitment agent once suggested that Pivot Tables and Vlookups were 'Advanced Excel'.

I can only assume the hiring company was run by Arts students or chimpanzees.

Bill Jelen would disagree.

Have been dabbling in Excel since it first came out. Used to spend hours perfecting formulae with any number of statements. In today's version of excel, it literally writes the formula for you. What was once advanced in Excel is now just basic excel but many people are unable or too lazy to learn the basics.

Pherekydes

Any 123 fans?

PhoenixParker

These days I skip Excel and go straight to R

Jayop

I'm an excel luddite compared to some people here, but the feeling that comes from writing a complex formula (complex for me anyway) and not getting "FALSE" is comparable to an orgasm.

=IF(O5<=1,(K5/100*3*40),IF(O5=2,(K5/100*4*40),IF(O5>=3,(K5/100*6*40))))

That little beaut was causing "FALSE" down the table where data hadn't been inputted in before I realised that putting the "<" in the first "IF" would sort it.

Huzzah for excel.

quickbeam

Ficheall wrote: »

People still use Excel? :pac:

Shurrup! Excel rules!!! :P

Northern Monkey

Jayop wrote: »

I'm an excel luddite compared to some people here, but the feeling that comes from writing a complex formula (complex for me anyway) and not getting "FALSE" is comparable to an orgasm.

=IF(O5<=1,(K5/100*3*40),IF(O5=2,(K5/100*4*40),IF(O5>=3,(K5/100*6*40))))

That little beaut was causing "FALSE" down the table where data hadn't been inputted in before I realised that putting the "<" in the first "IF" would sort it.

Huzzah for excel.

=IF(O5<=1,(K5*1.2),IF(O5=2,(K5*1.60),IF(O5>=3,(K5*2.4))))

Fixed that for you:D

Jayop

Northern Monkey wrote: »

=IF(O5<=1,(K5*1.2),IF(O5=2,(K5*1.60),IF(O5>=3,(K5*2.4))))

Fixed that for you:D

It works, but I'm a bit baffled. How does it still produce the right figure without the *40??

namenotavailablE

Or another variation

K5*SUM((O5<=1)*1.2,(O5=2)*1.6,(O5>=3)*2.4)

Edit to add: What is the expected result if O5 was somewhere between 1 and 2 e.g. O5 is 1.5?

Vladimir Poontang

Where should someone go to learn this stuff from, scratch?

Shenshen

Vladimir Poontang wrote: »

Where should someone go to learn this stuff from, scratch?

Google's your friend. Find yourself an example of what you'd like to do or calculate, and google it.
Nearly everything I know about Excel I learned through this, or through trial and error

5uspect

Excel? Geez, learn to code.

Pelvis

Vladimir Poontang wrote: »

Where should someone go to learn this stuff from, scratch?

TBH I wouldn't bother, not if you don't actually use excel regularly, and learning it won't help you achieve much of anything on it's own.

Learn a programming language instead, it's fun, and you can actually do something with it. I'd recommend Python or Java to start off with.

Jayop

namenotavailabl wrote: »

Or another variation

K5*SUM((O5<=1)*1.2,(O5=2)*1.6,(O5>=3)*2.4)

Edit to add: What is the expected result if O5 was somewhere between 1 and 2 e.g. O5 is 1.5?

Mind blown by this one. It's absolutely producing the correct figures.

O5 can only be a whole number.

OldMrBrennan83

This post has been deleted.

Jayop

Shenshen wrote: »

Google's your friend. Find yourself an example of what you'd like to do or calculate, and google it.
Nearly everything I know about Excel I learned through this, or through trial and error

As you can see from my getting slapped down there after posting what I considered a great formula just knowing a way of doing it and using google to help can produce the correct results, but people with proper experience who do it a lot can usually make the formulas much more elegant.

I'm still trying to figure out how the two shorter revised ones work....

Lia_lia

Lol. I was talking my my OH about vlookups the other day (I know right). He uses them in his job the odd time and he told me for ages he used to call them v-card lookups. I had to explain to him that "losing your v-card" is slang in the states for losing your virginity. He was wondering why people were looking at him funny when asking for help with v-card lookups!

namenotavailablE

Jayop wrote: »

I'm still trying to figure out how the two shorter revised ones work....

The formula I posted works as follows:

The bits in brackets (O5<=1), (O5=2) and (O5>=3) are turned into the words TRUE or FALSE by Excel when it's processing the formula (the term is 'Boolean' if you're looking it up). So, for example, if O5 is 2, it will give FALSE, TRUE and FALSE respectively-> O5 is not less than 1, so FALSE is the result of the first bit above in brackets; it is equal to 2, so that gives TRUE for the second bit above in brackets; finally, it's not greater than or equal to 3 so that returns FALSE

These TRUE/FALSE results are then multiplied by different values (1.2, 1.6 and 2.4 respectively). When you multiply the words 'TRUE' or 'FALSE' by a number in Excel, Excel treats those words as 1 or 0 respectively, so the result of the multiplication is 0 [FALSE*1.2], 1.6 [TRUE*1.6] and 0 [FALSE*2.4]. This sums up to 1.6.

Then the value at the very start of the formula, K5, is multiplied by 1.6... and voila

Dravokivich

Prefer to use get pivot. It references data instead of redoing calculations, so less memory intensive.

Ipso

Vladimir Poontang wrote: »

Where should someone go to learn this stuff from, scratch?

Youtube,the channel ExcelIsFun is very good but start from the earliest.