+ Reply to Thread
Results 1 to 18 of 18

vlookup with a row from another sheet

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    vlookup with a row from another sheet

    Hi guys,

    How do I get a vlookup to work on sheet1 tab, cell b1 down, whereby I`m trying to vlookup cell A1, and see if exists in my row on first tab, row 14?

    This what i wrote: =VLOOKUP("Sheet1!A1"&"*",SRM1155501.107!A14:DB14,1,FALSE)

    Pls see attached.

    Thanks
    Attached Files Attached Files
    Thanks,

    R.



  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    H1. In B1, copied down:

    =IFERROR(INDEX(SRM1155501.107!$14:$14,,MATCH(A1&"*",SRM1155501.107!$14:$14,0)),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: vlookup with a row from another sheet

    Try

    =IFERROR(INDEX(SRM1155501.107!$14:$14,MATCH(A1&"*",SRM1155501.107!$14:$14,0)),"No match")

  4. #4
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: vlookup with a row from another sheet

    thank you Glenn. Can you guide me on how to update the formula to look for partial match/es and return the value(s) for a partial match by checking the value in column A on sheet1 tab, and returning the closest match from the row on the first tab?

    thank you
    Last edited by rayted; 07-02-2021 at 11:35 AM.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    Partial matches can be messy... Are you talking about variability BEFORE the string in A1 as well as AFTER it... (e.g. "nice flavour" or nice flavour and expensive" or mis-spellings of the name of the string itself (e.g. "Prisms Type")

  6. #6
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: vlookup with a row from another sheet

    Hi Glenn

    BNasically does the word flavour (A1) exist in the row on the first tab and if so, where/what? nice flavour, flavour types etc

    ty!

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    As long as the spelling flavour is correct...

    =IFERROR(INDEX(SRM1155501.107!$14:$14,,MATCH("*"&A1&"*",SRM1155501.107!$14:$14,0)),"")

  8. #8
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: vlookup with a row from another sheet

    thank you Glenn, does excel allow you to return ALL the possible matches if there ar emore than one?

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    Yes, but that formula won't. Are all the possible "flavour" matches in the same row (row14) or are they on other rows? Are you OK if all flavour matches appear BESIDE other (col B, Col C, etc)?


    If BOTH answers are YES - say so. If EITHER answer is NO, say so and post a small sample sheet showing exactly what you mean/want.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    Assuming Yes/Yes:

    =IFERROR(INDEX(SRM1155501.107!$14:$14,,AGGREGATE(15,6,COLUMN(SRM1155501.107!$A$14:$DD$14)/(ISNUMBER(SEARCH($A1,SRM1155501.107!$A$14:$DD$14))),COLUMNS($B1:B1))),"")
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: vlookup with a row from another sheet

    Hi Glenn, your formula is great.

    I like it, would be great to have it in one cell, for easy reading.

    The data is all from one row (row 14 on the first sheet)

    Thanks!

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    To do that, you will need:

    1. Helper column(s) or

    2. VBA, or

    3. An upgrade to Microsoft 365.

    Which?

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    There is 4 th option that mihht be OK if the answer is < about 5...

    Maximum how many matches for any single search term? So maximum how many matches for (e.g.) flavour?

  14. #14
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: vlookup with a row from another sheet

    Hi Glenn,

    I have office 365 and never used the helper column and have no real knowledge of VBA!

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    First thing, then ... amend your profile. it's a real handicap if you display an out-of-date Excel version.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    Then use:

    =IF(A1="","",IFERROR(TEXTJOIN(", ",TRUE,FILTER(SRM1155501.107!$A$14:$DB$14,ISNUMBER(SEARCH(A1,SRM1155501.107!$A$14:$DB$14)))),""))
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: vlookup with a row from another sheet

    Hi Glenn profile updated and the formula seems amazing, ty so much!

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: vlookup with a row from another sheet

    OK! You're welcome and thanks for the feedback.

+ 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. [SOLVED] Delete sheet 2 with VLookup references and keeping the data in sheet 1
    By MaartenRo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-19-2021, 06:17 AM
  2. Replies: 4
    Last Post: 04-19-2020, 10:29 PM
  3. Replies: 3
    Last Post: 03-26-2020, 09:12 AM
  4. Replies: 6
    Last Post: 11-25-2013, 12:13 PM
  5. VLOOKUP table on one sheet that references every sheet in workbook
    By sportsman56 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2013, 12:22 PM
  6. Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1
    By leog1969 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-03-2013, 05:58 PM
  7. Replies: 7
    Last Post: 12-16-2012, 04:24 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1