VLookUp or something else?

[JICDB]

I'm using Excel Visual Basis 6.3 and am NOT a programmer however I have some advanced skills in Excel. What I have is a 2 worksheet file (I can put it on one if it works better). One sheet contains exported information from a database and contains 6 different columns. In each cell of the second worksheet I need to do the following:

In English: Search on this worksheet "Fares 2005" in this named range "FullFare" and if a record contains "East Division" in column G, and "1" in column c, and "521" in column A, return the number in D3

Basically I need to find a specific record that matches in columns G,C and A and then return D of that same record.

Vlookup may not be the way to go but I'm not sure how to do it in VB. I need to know the best way to do it in Excel and if is a Vlookup table how do I match 3 columns to return exactly what I'm looking for. If not what other way can I get the return I am looking for.

 

[Zathras]

If, for the three columns G,C and A, each combination is unique, the database function =DGET should be able to do this for you. (Assuming you have normal column names in the first row of the range.)

 

[JICDB]

I looked up DGET in the Excel help and am a little lost. The range of my datapool is huge (A2:G500) and instead of enter the info by hand into the adjoining spreadsheet I want it to search for the number that goes is each cell and populate that cell. example:

Route Loc Type Initial 1Xfer 2Xfer Division

I want to look in the entire range and find one record wehre Division = East, Type = 1 and Route = 521. I would then repeat this formula in each cell adjusting for the different route/type/division cominations. I'm sorry if this is confusing but I've looked at it too long today.

 

[Zathras]

Sounds like your best bet would be to create an additional temporary column in each worksheet that concatenates the three columns. Then you can use VLOOKUP to retrieve the value you want. Insert the new column to the left in the table on which you will be looking up.

After retrieving all of the necessary data you can copy and paste/special/values over the VLOOKUP formulas and finally delete the temporary columns.

 

[JICDB]

Works Great!!! Thanks! I'm going to leave the temporary columns there - just hide them.