Use data in a different worksheet to populate/replace data in another 1

[huytonscouser]

Hello, i have 2 worksheets 2 columns each.

I want to use the 1st column in one worksheet as the search criteria into the 2nd worksheet.
when i find a match(or not i'd want "not found") i want to insert into the original worksheet

i.e.

original worksheet(named Original) 2 columns
Task_Num Project Owning Division
1204.1 CORP - SMS
1907.8 CORP - SMS
1907.8 CORP - SMS
2030.1 CORP - SMS
2030.1 CORP - SMS
2030.1 CORP - SMS
2030.1 CORP - SMS


2nd worksheet (name Task Name Key)

Task Number Task Name
1001.0 Travel
1001.1 Expense
1200.2 Meetings/Calls
1202.1 Training Received
1202.2 Personal Development
1203.1 Internal Education
1203.2 Mentoring
1203.5 EMC Prov Prof
1204.1 Planned Absence
1204.2 Other Absence
1300.1 Presales Planning             


So in this example i'd like 1204.1 in the original to appear as
1204.1 Planned Absence
and not
1204.1 CORP - SMS



Is there a vlookup example someone could help me with ? If task_num is not found in Task Nme Key i'd like to display "Not found"

 

[SkipVought]

Hi,

I'm assuming Excel 2007+
[tt]
=vlookup(a2,'2nd worksheet'!A:B,2,false)
[/tt]
assuming that your '2nd worksheet' is named 2nd worksheet

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[huytonscouser]

Great, that works, thanks you very much.

How can i change the #N/A
to be a meaningful message ?

i.e.

=vlookup(a2,'2nd worksheet'!A:B,2,"not found in worksheet")

 

[SkipVought]

[tt]
=iferror(vlookup(a2,'2nd worksheet'!A:B,2,false),"meaningful message")
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[huytonscouser]

Excellent again !

1st worksheet may be variable in length. It's created from a database query and the output is a .xlsx file.

When the document is opened, how can i be sure that ALL rows in the 1st worksheet are popuplated via the lookup formula. I'd like to just open the spreadsheet, and it magically has the correct data from the 2nd worksheet.

also, instead of a meaningful message when i don't find a match, can i just leave contents untouched ?

 

[SkipVought]

Just reference that cell rather than a literal.

To be sure that all rows are populated, convert the table with your formula, to a Structured Table, a REALLY GREAT NEW FEATURE in 2997+.

Insert > Tables > Table

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[huytonscouser]

Thanks Skip 1st suggestion works fine to leave original data in place.

I don't undertsand 2nd part, could you kindly clarify.

Current formual is =IFERROR(VLOOKUP(A4,'Task Name Key'!A:B,2,FALSE),B5) this leaves data in place if a match is not found.

I drag this formula to all rows in worksheet, and i'm looking to make this task automated.
Thanks

 

[SkipVought]

If you make your table a Structured Table via Insert > Tables > Table, your formula will AUTOMATICALLY adjust to ALL ROWS in your Table. You don't have to fret about 'drag[ing] this formula to all rows in worksheet, and ... looking to make this task automated."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}