General discussion

Locked

Excel97 lookup/match/index help

By learneverything ·
I'm trying to modify the help I found at this web address to my needs.

http://www.j-walk.com/ss/excel/usertips/tip020.htm

I have too much information to put it in a pivot table or 2 variable table format. I'm actually trying to get it out of Access 97. If someone can help me with that, I'm open to it.

In lieu of that, I brought my Access information into my workbook on 2 spreadsheets. (There are 102870 records)

The information shows DirectoryCode in Column A, an ItemCode in Column B, and a Rate in Column C.

On another Worksheet, I need a formula in Column C that will display the appropriate rate for a Directory Code in Column A and an ItemCode in Column B.

What is the path of least resistance here?

Thanks all.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel97 lookup/match/index help

by DKlippert In reply to Excel97 lookup/match/inde ...

You could hack something together in Excel, by setting up a separate list/table of Codes and rates and then using VLOOKUP to match the two.
However, that amount of data cries for Access.
Set up a new table in Access with a list of Codes and rates.Find the unique identifier (Primary key). Now combine the tables using a Query.
This is what Access was designed for.

Collapse -

Excel97 lookup/match/index help

by learneverything In reply to Excel97 lookup/match/inde ...

I agree and would love to do the whole thing in Access. Our problem will be getting it back out in the ridiculous Excel Spreadsheet that was sent to us. The client insists it come back in the same form. I think we'll end up doing the bulk of it in Access Parameter Queries, cutting and pasting the results back in to a long and hideous Spreadsheet.

Thank you for your input. Those clients, they're always being right is sometimes and inconvenience.

Collapse -

Excel97 lookup/match/index help

by kees.valkenswaard In reply to Excel97 lookup/match/inde ...

I agree to the idea to use Access for this. Actually the combination DirectoryCode and ItemCode is the primary key in your base table. Linking both fields of the two tables is sufficient to get the desired result. You may have to set the join-type, thus requesting all records from your giant table and only the identical records from the other table. In the query design grid double click on the link to do that. You always could add additional criteria.
Suppose you have two tables, Giant and RateList.
In the query grid add both tables and make the link between the fields DirectoryCode in both tables and also between ItemCode in both tables.
In the grid below add the fields DirectoryCode of Giant, ItemCode of Giant, Rate of RateList and you with see all you need. Additional fields are possible of course. If you wish, it is possible with Tools, Office links to export the result of the query to Excel. Perhaps that is what you like to do. So don?t export the table. Rather export the result of the query that did the job for you.

Good luck.

Collapse -

Excel97 lookup/match/index help

by learneverything In reply to Excel97 lookup/match/inde ...

I agree and would love to do the whole thing in Access. Our problem will be getting it back out in the ridiculous Excel Spreadsheet that was sent to us. The client insists it come back in the same form. I think we'll end up doing the bulk ofit in Access Parameter Queries, cutting and pasting the results back in to a long and hideous Spreadsheet.

Thank you for your input. Those clients, they're always being right is sometimes and inconvenience.

Collapse -

Excel97 lookup/match/index help

by learneverything In reply to Excel97 lookup/match/inde ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums