+ Reply to Thread
Results 1 to 15 of 15

Vlookup and & Functions

  1. 02-05-2014, 01:12 PM #1
    lsm33000
    lsm33000 is offline
    Registered User
    Join Date
    07-16-2013
    Location
    Oxnard, California
    MS-Off Ver
    Excel 2007
    Posts
    44

    Vlookup and & Functions

    Good Day,

    I am trying to figure out a formula that look at two separate cells and if both cells meet a specific parameter, they return a third cell. Please see attached table. in the first sheet, we have our date table. The second sheet would display certification due. I'm thinking a vlookup to cells in column A and Row 1 on the second sheet, returning name and certs/dates (cell E in first sheet). What i want displayed is written in red. However, I can't figure out the formula.

    Please help if you can. Thank you.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-05-2014, 01:48 PM #2
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: Vlookup and & Functions

    I do not catch the B column with different names assigned to other. But as for certificates - Use array formula (ctrl+Shift+Enter committed) 
    Please Login or Register  to view this content.
    see attachment
    (I changed format in row 1 to reflect dates in column D)
    Attached Files Attached Files
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  3. 02-05-2014, 01:50 PM #3
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Vlookup and & Functions

    Hi,

    a first answer

    The formula try to match Names of column A and month


    In C2 and down and to the right

    Please Login or Register  to view this content.

    Hope it's a little step forward


    Edit: sorry Kaper, same formula (more or less)
    Attached Files Attached Files
    Last edited by canapone; 02-05-2014 at 01:53 PM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  4. 02-05-2014, 01:53 PM #4
    lsm33000
    lsm33000 is offline
    Registered User
    Join Date
    07-16-2013
    Location
    Oxnard, California
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Vlookup and & Functions

    Thaks. Those both work, except that an individual may have more than one certification per month.....
    Reply With QuoteRegister To Reply
  5. 02-05-2014, 02:02 PM #5
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Vlookup and & Functions

    Hi,

    in the file attached every cell can house up to three different certifications.

    Please Login or Register  to view this content.
    Formulas are array INDEX to be confirmed with control+shift+enter.

    I'm sure there are more convenient way to get the certifications
    Attached Files Attached Files
    Last edited by canapone; 02-05-2014 at 02:05 PM.
    Reply With QuoteRegister To Reply
  6. 02-05-2014, 02:04 PM #6
    lsm33000
    lsm33000 is offline
    Registered User
    Join Date
    07-16-2013
    Location
    Oxnard, California
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Vlookup and & Functions

    That might work! Perhaps I have more than three, can I increase that number? Thanks!!
    Reply With QuoteRegister To Reply
  7. 02-05-2014, 02:08 PM #7
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Vlookup and & Functions

    Hi,

    just add a segment to the formula changing the last parameter of INDEX


    &IFERROR(INDEX(Sheet1!$E$2:$E$7,SMALL(IF(Sheet1!A2:A7=$A2,IF(MEIF(Sheet1!$D$2:$D$7)=COLUMN(A$1),ROW($A$2:$A$7)-1)),4)),"")&ERROR(INDEX(Sheet1!$E$2:$E$7,SMALL(IF(Sheet1!$A$2:$A$7=$A2,IF(MEIF(Sheet1!$D$2:$D$7)=COLUMN(A$1),ROW($A$2:$A$7)-1)),5)),"")


    A pivot table could handle this job more ecologically: array formula do not always get along with the processor.
    Attached Files Attached Files
    Last edited by canapone; 02-05-2014 at 02:13 PM.
    Reply With QuoteRegister To Reply
  8. 02-05-2014, 02:08 PM #8
    lsm33000
    lsm33000 is offline
    Registered User
    Join Date
    07-16-2013
    Location
    Oxnard, California
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Vlookup and & Functions

    Perfect. I will play with that. Thanks.
    Reply With QuoteRegister To Reply
  9. 02-05-2014, 02:16 PM #9
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Vlookup and & Functions

    Hi again,

    thanks for your kind feedback. If you are free to write in column A three/four/five times each name formulas could easier to adjust. But I do not want modify the layout of your data.

    Regards
    Attached Files Attached Files
    Last edited by canapone; 02-05-2014 at 02:20 PM.
    Reply With QuoteRegister To Reply
  10. 02-05-2014, 02:21 PM #10
    lsm33000
    lsm33000 is offline
    Registered User
    Join Date
    07-16-2013
    Location
    Oxnard, California
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Vlookup and & Functions

    How do you mean? Feel free to adjust as needed, it is a sample table. This is genius stuff!
    Reply With QuoteRegister To Reply
  11. 02-07-2014, 02:06 PM #11
    lsm33000
    lsm33000 is offline
    Registered User
    Join Date
    07-16-2013
    Location
    Oxnard, California
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Vlookup and array

    I am still trying to set up an excel calendar that pulls in certifications and dates due. the fist book is (sheet 2) how far I have gotten, but my formula is not working. The second book is how i'd like the calendar to look.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 02-07-2014, 02:48 PM #12
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Vlookup and & Functions

    Hi,

    please see the file attached. Cells in D2:D5 should house 1 (see the formats).

    Just a little correction. More important is how to confirm formulas in sheet2

    Hope it's a little step forward.

    Regards
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 02-07-2014, 08:02 PM #13
    lsm33000
    lsm33000 is offline
    Registered User
    Join Date
    07-16-2013
    Location
    Oxnard, California
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Vlookup and & Functions

    I"m still not able to pull it in. One i enter the formula nd confirm that it is linked to the cell b1 for month, and shift,ctrl, enter. it gives me blank. Can you take another look? I am working with a list of 50 names. I just replaced them with numbers.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 02-07-2014, 09:41 PM #14
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Vlookup and & Functions

    Get it done, please see the files

    Cheers
    Azumi
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 02-11-2014, 03:50 PM #15
    lsm33000
    lsm33000 is offline
    Registered User
    Join Date
    07-16-2013
    Location
    Oxnard, California
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Vlookup and & Functions

    Thank you. This almost works perfect. But for instances where an individual may have more than one procedure that month. I have adjusted the lookp. Can you add more than one procedure per month? See , for example, individual 3. This person has two procedures in March.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vlookup with if functions
    By kygwalt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-22-2013, 12:25 PM
  2. Multiple IF Functions or VLOOKUP functions
    By yinka00000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2012, 11:29 PM
  3. vlookup functions within if functions.
    By sjhill60 in forum Excel General
    Replies: 1
    Last Post: 10-13-2011, 11:10 PM
  4. VLOOKUP and IF functions
    By Erin4280 in forum Excel General
    Replies: 3
    Last Post: 10-30-2009, 05:50 PM
  5. IF and vlookup functions
    By cr41g in forum Excel General
    Replies: 9
    Last Post: 09-21-2007, 07:46 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1