Matching Excel Fields

[jjwal100]

Hi

I have a spreadsheet with two tabs and I want to be able to match the data in the first table with the data in the second table on the second tab. I have tried using VLookup but it is to complicated to use and update. Is there an easier way to simplify the process, maybe using a macro or VBscript??

Any help will be appreciated.

 

[SkipVought]

Hi,

VLOOKUP is not at all complicated. It is certainly simpler that any marco that you might have to use.

You supply the lookup value in the first argument, specify the lookup table reference in the second argument, WITH THE FIRST COLUMN containing the values to lookup and argument three is FALSE if you want an exact match.

Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[jjwal100]

Thanks Skip

Sorry Vlookup is simple to setup but I mean from the user point of view is there a more simple way, where they can press a button and the fields then match?

Also, the data on the second tab is imported from another application which means the cell ranges may change which will affect the lookup.

JJ

 

[SkipVought]

"...the cell ranges may change ..."

Use a DYNAMIC Named Range. faq68-1331.

You could also use MS Query. faq68-5829.

Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[AnotherHiggins]

JJ:

Just trying to figure out why you don't think VLookup will work for you.

If you describe in more detail how you envision it (or whatever else) being used, we can offer better advice. How will the users interact with the spreadsheet and what should it do?

Is the problem that they'll be adding new lines to column A, and you want values to automatically populate column B, C, etc.?

If that's the case, then go to Tools > Options, on the Edit tab, check the box beside extend data range formats and formulas. You can look that up in help for more info, but the basic idea is that as you type in more rows of data, the formulas are automatically copied into the new row.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John,

I think that he's referring to the IMPORTED data range that changes.

If you use Edit > IMPORT..., then Excel inserts a query table (one time) AND Excel NAMES the imported range dynamically. You can REFRESH the import at any time. VOLA!!!

Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[AnotherHiggins]

Skip,

Now that I re-read his post, you're right. I thought I read it before, but I must have just skimmed it.... That's what I get for trying to quit caffeine.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Ahhhhh, I'm on my SECOND cup!!!

Skip,

_{Just got a nuance...
to replace the old subtlety!}