+ Reply to Thread
Results 1 to 5 of 5

Formula Help matchin colum a with colum b to display colum c

  1. #1
    Registered User
    Join Date
    05-08-2013
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Formula Help matchin colum a with colum b to display colum c

    Here is what I am trying to do I have 4 columns A which displays names; B which displays a corresponding id number to the name in A. I also have column D which has more id numbers and column E which has email addresses that correspond with the ID numbers in column D. I do not have email addresses for each id number so there are less values in columns D&C than A&B. What I want to do is match the ID number In B With the ID# D and display the corresponding email address from column E in column C.

    Seems simple enough but I have tried a hundred things and cant get it to work. looking fro a fresh start, any help is appreciated. Thanks

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: Formula Help matchin colum a with colum b to display colum c

    try the index/match
    change range to suit needs.
    =index($E$1:$E$100,match(B1,$D$1:$D$100,0)) ->fill formula down.

    edit:

    if error will be encountered use the iferror...

    =iferror(theforuma index/match,"") -> the "" means null value or blank.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    05-08-2013
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula Help matchin colum a with colum b to display colum c

    Hey that worked great, except some of the cells in column C are displaying Zeros, any Idea Why?

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: Formula Help matchin colum a with colum b to display colum c

    since there is no e-mail to return to it will display 0.

    =iferror(if(index/matchformula=0,"",index/match formula),"")

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula Help matchin colum a with colum b to display colum c

    Quote Originally Posted by dbe82 View Post
    Hey that worked great, except some of the cells in column C are displaying Zeros, any Idea Why?
    Empty cells evaluate as 0.

    Try this tweaked version of vlady's formula:

    =T(INDEX($E$1:$E$100,MATCH(B1,$D$1:$D$100,0)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1