MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP on Multisheet Workbook


Posted by Bob on September 05, 2001 2:29 AM

I'm an intermediate Excel user. I am working on a project for my office (I work on Wall Street) that would utilize the VLOOKUP function. The catch is I would be trying to look up a table on another worksheet in the given workbook.

What I would be building would be a template for a daily report of client positions. The way I see it, I will need three worksheets on the template,

Sheet 1: Capture raw data extracted from mainframe that will have a standardized format (but varying amount of rows), but will have more columns than needed for the report. This could be eventually automated, but for now I can copy paste this info onto my template from the data source

Sheet 2: Use paste special function to take only the columns needed (account number, description of position, net position and date) from Sheet 1. In addition, next to the account number, I want to have a section for client name (which is not including in the imported extract information coming into Sheet 1. This will be looked up from Sheet 3

Sheet 3: This would be my reference table with only two columns listing account number and client name. I would input this information manually.

I could put the reference table on another part of Sheet 2 so that I would only need two sheets, but because I will probably do so much sorting and probably delete some rows, that might disable the reference table.

If I were better at Microsoft Access, it would be better to create a relational database for this project. However, since nobody else in my office uses Access, I am trying to make this an all-Excel project

Anyone know how I can get this done? What would be the syntax for the column that would use the VLOOKUP function?

Thanks

Bob



Posted by Aladin Akyurek on September 05, 2001 2:44 AM

Bob,

It's

=VLOOKUP(account-number,reference-table,2,0)

You put this formula in sheet 2 next to the first account number in order to get the name of the client. Replace "account-number" with the cell ref of the first account number that you have in sheet 2. Replace reference-table with the actual range that you used in sheet 3 (excluding labels/column headings).

Aladin

========