lookups in Excel

[kingpaul]

I run a report for my boss. However, I will soon be in training, and won't be able to run it for him. I merge, in Access, 2 tables that are linked on 1 field, run a query, and print the report. Now, the person that'll be doing it for me dislikes Access, and wants to know how to do it in Excel. I don't know Excel all that well, but I know that there are LOOKUP, VLOOKUP, HLOOKUP and MATCH commands, but don't know the intricacies involved. Can anyone help me out here?

 

[Ayrk]

Well the Vlookup and Hlookup are pretty easy.

The function is vlookup(what to lookup, where to look it up, what column to display)

The table you are looking things up in must be sorted by whatever you want to look up. The main difference between vlookup and Hlookup is that in vlookup the differents fields are in different columns whereas in hlookup they are in different rows.

To put it in d20 terms, assume the following table:
Barbarian d12
Cleric d8
Fighter d10

If I entered Fighter in A1 and used vlookup(a1,table,2), it would go to the section of the spreadsheet called table, look for fighter in the first column, and print out what was in the second column, in this case d10.

Hope that helps.

Erik

 

[Heretic Apostate]

But don't forget that, unless your xLOOKUP function has the nebulous and rarely-used fourth item (x,x,x,FALSE), the lookup will return the closest thing to what you're searching for if it can't find what you ask for.

For instance,

Barbarian d10
Cleric d8
Fighter d10

If you searched for Bard, it's not in the table. So it'd settle for the next lowest item, Barbarian, and return d10.

So if you use VLOOKUP(item you're searching for, table to search, column of table to return data, FALSE), you'll get back exactly what you're searching for if it's there, or an error message if it isn't.

I think that the FALSE also means that the table doesn't have to be in some sort of order. So I could have,

Barbarian d10
Wizard d4
Cleric d8
Sorceror d4
Fighter d10
Bard d6

and so on, and it'd return d6 for Bard, even though it's not in order.

VLOOKUP is one of my favorite functions.

 

[Shard O'Glase]

actually excell can run queries. You could copy your access querry and load it inot excell somehow. Then you just set up the parameters for how it refreshes. Like once you type in the site number or something. I'm much more familiar with access for queries, but I got a few of these excell queries on my computer, I'll see if I can figure out how they are done.

I just know that for example my slaes history excell queery, I type in cust number then the date range, once the end datee range is in the queery runs and it instnatly slips into the excell sheet set up. It meges 3 tables and is really easy to use. Heck I think they even have a query wizard for those who don't know sql at all.

 

[Brent]

The fouth clause can be set to TRUE to do a nice range lookup, I use this a lot for DnD type tables:

Table
1 TextA
3 TextB
6 TextC
9 TextD

A1= ROUND((RAND() * 9)+1,0) <generate random number 1-10>
A2= VLOOKUP(A1,Table,2,TRUE) <Returns TextX based on probability in first row of table>


If you do want an exact match then I reccomend:

IF(ISERR(VLOOKUP(Value,Table,column,FALSE)),$$$$,VLOOKUP(Value,Table,column,FALSE))
Replace $$$$ with what value you want if Value is not found in the first column of Table (that way you never get an error). So if you replace $$$$ with '0' you can still sum a row, or you can even replace $$$$ with Value & " not found." to get a nice 'Jim not found. (if Value = Jim).

Anywho, i believe I have rambled eneugh...

 

[Heretic Apostate]

Yeah, ISERROR is a great function!

In a spreadsheet I made recently, I wanted to give the users the option of searching for text strings that may occur anywhere (if at all) within text of indeterminate length.

So, I used:

IF(ISERROR(SEARCH(text searched for,text searchedin)),not there message,string found message)

That way, they were able to search for individual words or parts of words or whatever.

Turned out to be useful. That spreadsheet took a process that could take up to several hours, was labor-intensive, and very error-prone, and reduced it to less than 15 minutes, all automated, and all calculations are well controlled. They absolutely love it.

Of course, it's clunky and bloated, but what the hey. It's at least an order of magnitude better than doing it by hand. And I gave it to them for free, as a Christmas present.

 

[Davin]

I run a report for my boss. However, I will soon be in training, and won't be able to run it for him.

Might it also be possible for you to put your procedure in an Access macro and just encapsulate the whole thing so the Access-phobic person doesn't even know what they're running?

 

[Sir Whiskers]

I'll second Davin on this. Creating a simple interface for novices is a great idea whenever using Access. I'm assuming your report is tied to the query, so you don't have to manually merge tables or anything to run it (in other words, you just run the report, print it, then close it). If so, consider:

Create three macros, named "mcrRunRptZZZ" (where ZZZ is the name of the report), "mcrCloseForm", "mcrExitAccess". Each macro will have just one command.

mcrRunRptZZZ: Open Report (specify the report name and whether you want it to print or appear in print preview)

mcrCloseForm: Close (this will close the menu form you'll create in a minute)

mcrExitAccess: Exit (this will close Access completely)

Create a simple form with just three command buttons for now (use the toolbox to place them on the form). The three buttons will be named (caption property): Run Report ZZZ, Close Form, Exit. Tie the proper macro to each button with its OnClick event property.

Then just set the startup properties in the database to display this menu form when the db opens. Your boss can click a button to run his report, then click Exit to exit the db. You can use the Close button to close this form when you want to get into the db.

I can't tell from your posting how familiar you are with macros and command buttons, so the directions I listed above may be unnecessary. On the other hand, if you decide to do this and need any help, feel free to contact me at sirwhiskers@worldnet.att.net, or just post on this board.

Good luck.