Vlookup nightmare

[guideme]

Help with the formula!!
ok I have 3 columns in Sheet2 (or maybe more in the future).
I have 2 worksheets. One for the lookup formula-> Sheet1 and Sheet 2
for all the data such as the area code->A, salesrep->B and region ->C.

On Sheet 1, A2, I typed the formula
VLOOKUP(A1,area2!$A$2:$C$295,2,FALSE)
But it's only giving me the salesrep, I also want the region.
How do I change my formula to include the region as well, AND maybe
I'll add more columns such as phone numbers of the salesreps, how can I
change my formula to look up more columns when I type a single area
code?

thanks!
I hope I made myself clear...

 

[guideme]

wow that worked ! thanks.

Instead of putting in say, B1:

Put in B1:
=VLOOKUP($A1,area2!$A$2:$C$295,COLUMNS($A$1:B1),FALSE)
Copy B1 across to C1, fill down as required

Col B will return the sales reps, col C returns the regions

 

[L. Howard Kittle]

Another way is to select the three column cells (or more) you want the
results to appear in and while selected type in this formula. Now hit CTRL
+ SHIFT + ENTER.

=VLOOKUP(A1,F1:I5,{2,3,4},0)

The lookup value is entered in A1 and the lookup array is F1:I5, returns the
values in G, H and I.

If you think, in the future, you are going to expand the number of items to
lookup for each person then you may try something like this formula.

=VLOOKUP(A1,F1:N11,{2,3,4,5,6,7,8,9},0)

In this case you would select 8 cells across the columns and array enter the
formula. If you only have 4 columns of info on a sales person then 4 bits
of info would be returned and the other cells will be 0's. The next sales
person may have 7 items of info with his name so you would get 7 items
returned and one cell with a 0.

Remember, if you want to change the formula you have to select all the cells
holding the formulas and array enter after the change. If you get stuck in
a single cell and the pop up warning that you cannot change a single item of
an array, then hit ESC and proceed from there.

HTH
Regards,
Howard

 

[Dave Peterson]

Another option.

Dedicate one column to check to see if there's a match--and if there is, to show
the row where the match occurred.

So you could do this:
=match(a1,area2!a:a,0)
(say you used column D for this)

Then you could use that column to return the corresponding value from other
columns:

Then in column E:
=if(iserror($d1),"",index(area2!b:b,$d1))

And drag to the right (and down)

If you have lots of columns to return (for lots of rows), this seems to be
quicker than doing =vlookup() for each cell. (Well, at least to me.)

And you could hide column D if you find it irritating.

 

[aaron.kempf]

vlookups are just an example of where microsoft went wrong with excel

when you start using vlookups is when you need to throw away excel and
start using a real program-- like access

 

[Harlan Grove]

(e-mail address removed) wrote...

vlookups are just an example of where microsoft went wrong with excel

....

VLOOKUP with no 4th arg or 4th arg TRUE or 1 functions the same as the
@VLOOKUP function in Lotus 123, which is intentional. 123's @VLOOKUP
goes all the way back to Release 1.0 in, what, 1982? What, oh great
expert, were the DBMS options for PCs with 256KB RAM in 1982?

@VLOOKUP was pretty good back then, and Excel's VLOOKUP works today as
@VLOOKUP did then, preserving backward compatibility, which is MUCH,
MUCH MORE IMPORTANT than your petty aesthetic opinions.

 

[aaron.kempf]

no but seriously.

when they first came out with vlookups-- that is when some product
manager for excel should have said 'whoa' and 'wtf are we trying to do
with relationships in a spreadsheet'

that is when they crossed the line

and if i could build a time machine-- i would kick and scream and not
let them do it.

because that is how this disease started.

you know 12 monkeys-- the awful virus was going to take over the world?

i claim that movie was about Microsoft Excel-- except the disease of a
program has much more mindshare than it deserves

time go get into databases kids.. it's no longer 1994

lol

 

[Harlan Grove]

(e-mail address removed) wrote...

no but seriously.
!!?

when they first came out with vlookups-- that is when some product
manager for excel should have said 'whoa' and 'wtf are we trying to do
with relationships in a spreadsheet'

....

Excel 1.0 came out in 1984 or 1985 initially for 512K Macs. What, oh
great expert, were the databases that could run on such machines?

You lack an understanding of the context.

Again, backwards compatibility is FAR MORE IMPORTANT than your opinions.

 

[guideme]

ok, here's another one, what about daylight savings time?
how do I incorporate that to the sheet?
say... Arizona, they are currently in Pacific time but when California
reverts back to DST, are they ahead or behind Pacific?
how can i formulate that in excel sheet?

 

[aaron.kempf]

dude storing or calculating PST is trivial in a database

and backwards compatability with a DISEASE is still a DISEASE

 

[Harlan Grove]

(e-mail address removed) wrote...

dude storing or calculating PST is trivial in a database

It's trivial in any application that provides any sort of list
processing AS LONG AS the list of zip codes or latitute and longitude
coordinates and time zones already exists. It's a major PITA even in a
database if you're the one CREATING such a table.

and backwards compatability with a DISEASE is still a DISEASE

More BS. If a feature was created in the dim past when there were NO
ALTERNATIVES, and that feature worked reasonably well in the
INTENTIONALLY RUDIMENTARY circumstances for which it was designed, then
it wasn't a disease. (The only disease here is your mental disorders.)
It's nowhere near as sophisticated as SQL queries, which have become
available to nearly all microcomputer users now. However, there are
still some models written LONG AGO using the rudimentary features that
were OK at the time that are still used, so backwards compatibility is
a GOOD THING.

Putting it a different way that you still won't understand (because you
don't want to), E. F. Codd and C. J. Date (you do know who they are,
don't you?) have stated that SQL is a poor (or very poor)
representation of the relational calculus. If someone comes up with a
better representation, does that mean all the SQL code in the world
would need to be rewritten over night to use the better representation?

 

[aaron.kempf]

well the thing is that SQL is portable

and Excel is a dead end street

you can take your logic out of an Access database and move it to SQL
Server; mySql-- oracle; anythign else in the world.

but Excel-- is a dead end street. and Excel is a disease.

50% of corporate America uses Excel every day-- making the same report
week in and week out
and my mission in life is to kick them out of employment.

-aaron

 

[aaron.kempf]

well the thing is that SQL is portable

and Excel is a dead end street

you can take your logic out of an Access database and move it to SQL
Server; mySql-- oracle; anythign else in the world.

but Excel-- is a dead end street. and Excel is a disease.

50% of corporate America uses Excel every day-- making the same report
week in and week out
and my mission in life is to kick them out of employment.

-aaron

ps - databases can do anything that excel can; even better-- and MDX
just blows Excel out of the water.
MDX has taken over the world and Excel dorks are too lazy to grow into
the future

 

[Harlan Grove]

(e-mail address removed) wrote...

well the thing is that SQL is portable

Not perfectly so. Try running a SQL query with a COUNT(DISTINCT ..) in
Access. IIRC, there are other subtle differences between DB2 and
Oracle.

and Excel is a dead end street

Conceptually, Excel is a functional language with a built-in (and
inflexible) grid control. It's got a long useful life ahead of it
because it's easier to learn than most databases, and the databases
that are nearly as easy to learn are underpowered and can't cope with
heavy duty calculations.

you can take your logic out of an Access database and move it to SQL
Server; mySql-- oracle; anythign else in the world.

See prior comments about the imperfection of SQL portability.

....

. . . making the same report week in and week out . . .

....

*IF* Excel were only used for generating reports, you might have a
small grain of truth in your rants. Maybe the people *YOU* work with
only use it to generate reports, but it's clear you know very little
about the breadth of its use and applicability.

 

[Harlan Grove]

(e-mail address removed) wrote...
....

ps - databases can do anything that excel can; even better-- and MDX
just blows Excel out of the water.
MDX has taken over the world and Excel dorks are too lazy to grow into
the future

OK, genius, answer the question posed in

http://groups-beta.google.com/group/microsoft.public.excel/msg/ac06db4892597489?dmode=source&hl=en

(or http://makeashorterlink.com/?F2E21234B ).

If MDX has taken over the world, how could there still be so many Excel
users?! Or, for that matter, why would you be ranting on & on & on?

As pointed out months ago, if data is already in nice neat tables,
maybe databases make more sense. Otherwise, the flexibility of
spreadsheets often (usually) beats the rigidity of databases.

 

[aaron.kempf]

i can do a distinct(count) in Access about 100 different ways...
first off i could do a subquery--

flexibility of spreadsheets isn't all glamorous-- and im more flexible
with Access than you are with Excel.. i mean-- i build something that
works week in and week out.. and you build something that you have to
change every week

you call that flexibility?

but the real meat and bones for count(distinct) is doing this through
either SQL Server (again, MSDE-- freeware SQL Server is FREE with
Access license) and i could do that in ADP without even batting an eye.

If i was really hard-pressed to do this against a billion records; i
could do this in Analysis Services in my sleep..

and i wont crap out when i hit 64k rows lol

what a joke of a product.. i mean-- come on.. 64k rows; what the heck
am i supposed to do with 64k rows?

 

[aaron.kempf]

btw, i woudlnl't do that in ONE query.. i could do anything like that
in multiple queries
i mean

Excel is SOOOOO 1982 i mean-- grow the hell up and learn somethign
useful; excel dorks

 

[Harlan Grove]

(e-mail address removed) wrote...

i can do a distinct(count) in Access about 100 different ways...
first off i could do a subquery--

I know you can do subqueries, and I know there are several workarounds
for Microsoft Access's limited functionality. But standards are good,
and Access is about as far from the ANSI SQL standard as any database
claiming to provide SQL functionality.

By your own criteria (no, I don't expect consistency from you), you
should be complaining about what a crappy job Microsoft has done
implementing standard SQL in Access.

flexibility of spreadsheets isn't all glamorous-- and im more flexible
with Access than you are with Excel.. i mean-- i build something that
works week in and week out.. and you build something that you have to
change every week

Periodic updating of data is a necessity in all systems. Otherwise all
one needs to produce a new report is a copier, and that'd be even
easier than using a database. Updated data isn't always available via
DELETE, INSERT or UPDATE queries, because data isn't always already
available in ODBC data sources.

Do I use the same spreadsheet templates repeatedly? You bet! Do I
change the formulas in them? Sure, when I come across errors. Do I
change formulas for other reasons? Nope, and haven't in years. How
often do I fix formula errors? Less frequently than once a quarter.

Do I have to perform manual data entry? Sadly, yes, because most of the
data I need to use I receive from customers who don't have access to
our systems, and we don't have access to theirs (which is the normal
state of affairs for those of us trusted to work with external
customers, rather than unpleasant bozos like you). Aside from expense
reports, I don't generate reports.

Do I create new spreadsheets? Yup. How often? Daily. Am I reinventing
the wheel every day? Depends on how you define it. I have libraries of
template formulas and data structures which I bring into new workbooks
by copying worksheets from library files into those new workbooks.
That's reuse as far as I'm concerned, though I do need to change range
addresses and name definitions, but that's akin to needing to change
field and table names in canned queries in databases. Typos can happen
in spreadsheet formulas just like they can heppen in SQL queries.

You don't appreciate the flexibility spreadsheets provide because you
don't really understand how to use spreadsheets, and you've established
that you lack the wit ever to learn. You also fail to understand that
spreadsheets can be used in disciplined ways (there I'll grant that you
may never have worked with anyone who knows how). It's not the tool,
it's how you use the tool. I don't deny that if Excel were a power
tool, there'd be a lot of one-eyed, three-fingered spreadsheet users,
but I do take issue with the position that it should be banned because
it's dangerous. The most useful tools tend to be the more dangerous
ones.

you call that flexibility?

No, because *AGAIN* you fail to understand what the term means.

but the real meat and bones for count(distinct) is doing this through
either SQL Server (again, MSDE-- freeware SQL Server is FREE with
Access license) and i could do that in ADP without even batting an eye.

....

'With Access license', so not free. Try PostreSQL if you really mean
free software. And you wouldn't be doing this in Access, but having to
install and use yet another piece of software to make up for Access's
limitations.

Dunno, maybe the ideal would be having a real SQL server (note lower
case) and using Excel as the UI. Oh, that's right, that what I already
use (the company I work for uses Oracle and DB2).

what a joke of a product.. i mean-- come on.. 64k rows; what the heck
am i supposed to do with 64k rows?

Analysis, something it appears you're incapable of understanding much
less performing.

I've never used more than 2,000 rows when I've had the chance to set
the original workbook design.

If a given task is better handled in a database, use a database. If
it's better handled using a spreadsheet, use a spreadsheet. If it's
better handled by a Perl script, use a Perl script. USE THE BEST TOOL
FOR THE TASK. Since you only know one tool, you're trapped believing
it's the ideal tool for every task.

 

[aaron.kempf]

harlan so you think that excel is the best tool

you think that access is 'too hard' to use and yet you talk smack about
postgres?

do you smoke crack?

access is the best, easiest to use db product in the world. all data
'should' be in ODBC-- i'm sorry that you have a bunch of excel dorks
for friends.

put everything in ODBC and your life becomes a lot easier

 

[Harlan Grove]

(e-mail address removed) wrote

harlan so you think that excel is the best tool

Not all the time, no. I've been pretty caustic about using it for
systems admin, text processing, even what should be database work. But
for what it does do well, free-form numeric calculations, it WAY EASIER
than any database.

you think that access is 'too hard' to use and yet you talk smack about
postgres?

You're confusing different points. For most Excel users, both Access
and PostgreSQL (and Oracle, DB2, and name your RDBMS flavor) represent
unknown territory, so are more difficult to use than the crude but
known tool (much like you and spreadsheets).

For me, RDBMSs and SQL are part of my toolset. I use Access for ad hoc
queries (because I'm lazy and find the query builder faster for simple
queries than writing SQL queries), but I'd never build a system for
other users based on it.

access is the best, easiest to use db product in the world. all data
'should' be in ODBC-- i'm sorry that you have a bunch of excel dorks
for friends.

Who says I have any friends?

AFAIK, my friends don't use Excel except perhaps for personal finance,
and Excel most definitely does make a MUCH BETTER financial calculator
than Access.

As for Excel developers, I do work with some, and it's my ongoing
project to get them to do things my way. However, they, unlike you,
know Excel is better for some things than Access (or Word or whatever),
know when it isn't, and have demonstrated some capacity for learning
how to improve their usage of Excel.

put everything in ODBC and your life becomes a lot easier

Perhaps, but if the choice is direct entry of hierarchical information
(best description of the sort of stuff I get from customers) into Excel
or into Access, I'll choose Excel because I know which is easier.

You need to realize that it's a function of just how far down the
ladder you are that you have most of what you need via ODBC.