+ Reply to Thread
Results 1 to 5 of 5

Vlookup multi date in one cell

  1. #1
    Forum Contributor
    Join Date
    07-27-2017
    Location
    Dhaka
    MS-Off Ver
    MS Excel 2019/ 64 bit
    Posts
    159

    Vlookup multi date in one cell

    Dear Expert,
    In my attached file my data table range is A1:D17
    column b contains my vlookup criteria & column A,C & D is my criteria range.
    column a contains date which is sequence like oldest to newest.
    column c & D also contains date but its no sequence, can be same always or same.

    In my attached file f3 is my lookup value, i need g3 & h3 result which match column c & d based on lookup value.
    if lookup value matches c or d column all data are same then it will take it will take any one or first cell data but if lookup value matched multi date then it will take all date & separe "/".

    i3 will lookup value first match data on column A & j3 will lookup value last match data on column A.

    In attached file i do it manually which is for your ref. but need it with formula.

    Please help me in this regard.


    Best Regard
    Wahid.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Vlookup multi date in one cell

    Please try at
    G3:H3

    =SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(AGGREGATE(15,6,C$2:C$17/($B$2:$B$17=$F3),1),"d-mmm")&TEXT(MAX(INDEX(C$2:C$17*($B$2:$B$17=$F3),))," d-mmm"),TEXT(MAX(INDEX(C$2:C$17*($B$2:$B$17=$F3),)),"d-mmm"),,2))," ","/")

    I3
    =INDEX($A$2:$A$17,MATCH(F3,$B$2:$B$17,))

    J3
    =LOOKUP(2,1/($B$2:$B$17=F3),$A$2:$A$17)
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    07-27-2017
    Location
    Dhaka
    MS-Off Ver
    MS Excel 2019/ 64 bit
    Posts
    159

    Re: Vlookup multi date in one cell

    Dear Expert,
    Your code is working properly according post 1 but when my table range is increase H column data is not coming.
    In my attached file previous i am working data row up to 17 but my actual sheet data row upto 1955, when sample worksheet data row extend up to 1955 h3/h4/h5 result not coming accordingly.

    Another need to correction like G & h column data is corect on your last provided sheet but here coming only 2 date oldest & newest but i need all unique data of C & D column considering lookup value.

    Attached file for your ref.

    Please pay your kind attention.


    Best Regards
    Wahid
    Attached Files Attached Files

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Vlookup multi date in one cell

    Without the Textjoin function, helper column is needed to concatenate text.

    E2:F2
    =SUBSTITUTE(IFERROR(TEXT(C2,"d-mmm")&"/"&VLOOKUP($B2,$B3:E$1954,COLUMNS($B2:E2),),TEXT(C2,"d-mmm")),"/"&TEXT(C2,"d-mmm"),)

    H2:I2
    =VLOOKUP($G3,$B$2:E$1953,COLUMNS($B2:E2),0)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-27-2017
    Location
    Dhaka
    MS-Off Ver
    MS Excel 2019/ 64 bit
    Posts
    159

    Re: Vlookup multi date in one cell

    Dear Expert,
    Thanks for you above support. I need another help similar to this, i just want to change data format on C & D column to numeric(1/2/3) or text(A/B/C) or both format from date format that i used earlier. then what will be the revise formula?
    Please pay your valued attention.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Multi value Vlookup in same cell
    By fbenzoni in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-05-2019, 06:05 AM
  2. Date formating + Multi column+ Multi format, date combining+
    By ajiljay in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2016, 07:26 PM
  3. [SOLVED] VLOOKUP multi cell
    By davidmg_13 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-06-2012, 12:01 PM
  4. multi cell vlookup
    By kayslover in forum Excel General
    Replies: 9
    Last Post: 03-28-2011, 05:31 AM
  5. Excel 2007 : VLookup date range and return multi lines
    By rik1603 in forum Excel General
    Replies: 1
    Last Post: 02-18-2010, 11:04 AM
  6. [SOLVED] How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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