Search code examples
databaseexcelorganization

Microsoft Excel Column Search and Return

Lets say i have 2 sheets one containing: Data for some info on products for emails,

p159    Ben54@ya
g456    Jo32@gm
k455    Jack45@ro
t888    Sally12@k

And a second sheet containing the code and the name the email has with it,

k455    Jackson
p159    Benjamin
g456    Joseph

Keep in mind there is alot more information and it is all scrambled about but organized into columns. Also some codes have an email but no name.

Now i need to combine in a sheet or column these Emails with the names given by finding the code from sheet one and sheet 2 and taking the info from that row and spitting it into a cell in a fashion such as

Jack45@ro:Jackson

So in the other row in no particular order (organization in the column of the emails:name isn't important) it should look like so 

Jack45@ro:Jackson
Jo32@gm:Joseph
Ben54@ya:Benjamin

Notice that Sally was omitted because she doesn't have a name to her email(I would rather ignore her info & its alright if that row gives #VALUE errors)

I have experimented but i keep getting errors #VALUE ?NAME #N/A Because i am grabbing fx from others and am having trouble formatting it properly.

TL:DR I need to search a column for a code then combine the data in the cell next to it with the cell next to the code in the other sheet.(I don't know if i typed that properly ;/)

So if anybody could make a function for this it would be greatly appreciated :)

Solution thanks to glh! Also from his function in my case I had to change the false's to true to fix some #N/A error.

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",B1&VLOOKUP(A1,Sheet2!A:B,2,FALSE))

I used

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,TRUE)),"",B1&":"&VLOOKUP(A1,Sheet2!A:B,2,TRUE))

Helpful tip...

If the data you are drawing from is derived from another function and when you select it you don't see the text you do in the cell, copy and paste the section and when you paste it select the paste with values and number formatting option from the small clipboard that pops up

Solution

  • Sample data;

    enter image description here

    If you use:

    • Excel 2007 +: =IFERROR(B1&VLOOKUP(A1,Sheet2!A:B,2,FALSE),"") in cell C1 in Sheet 1.
    • Excel 2003-, =IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",B1&VLOOKUP(A1,Sheet2!A:B,2,FALSE))

    Output:

    enter image description here