'Compare data and append' Problem

[Kryten]

Hi,

I really hope that there is an easy way to do this..!

I have a big sheet of data with maybe 60 columns and 3500+ rows all
showing relative values
to the headers in the first row of each column. It's actually data
about how a telephones are configured
on a large system, so the headers contain things like Extn Number,
Port Number etc etc. Most of the columns are actually pretty
irrelevant.

Now, I have another sheet that only contains two columns defined as
'Extn number' and 'Desk Location'. This is valuable data that's hard
to come by.

I want to do some sort of lookup on the first sheet ( Extn Number )
value to see if that value appears in the second sheet and if so
append the corresonding value for the desk, again this is available in
the second sheet in the next column to where the match was just found.

I can, rather obviously, manually add a new column to the first sheet
called 'Desk Location'. This is where I'd like the desk location value
to be dropped from the second sheet.

So when finished the first sheet will only have populated values in
the 'desk location' column where a match was found in the Extn Number
column from the second sheet.

Would really appreciate any help or pointers into how best to achieve
this.

I can supply sample sheets if needed.

Many thanks,

Kryten

 

[Pete_UK]

If your desk location column is to the right of the extension number
column in your big sheet, then you can use VLOOKUP to do this. If it
is to the left then you will have to use an INDEX/MATCH combination.
Guessing the columns you are using as E for the extension and F as the
desk location on a sheet called "main sheet", and that you have an
extension number in A2 of your other sheet, then put this in B2:

=IF(ISNA(VLOOKUP(A2,'main sheet'!E:F,2,0)),"",VLOOKUP(A2,'main sheet'!
E:F,2,0))

Copy down as required.

Hope this helps.

Pete

 

[Kryten]

Job done!
Thanks Pete!
Very Much Appreciated.

Stuart

 

[Pete_UK]

You're welcome, Stuart - thanks for feeding back.

Pete