Results 1 to 4 of 4

Thread: Vlookup (or other) with blanks in columns

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup (or other) with blanks in columns

    Hello,

    Apologies if this has been asked before, I may not be using the best key words to search for. I have a form which has been filled out by people and the form responses are outputted to an excel file. I want to search this file for the entries made by specific people and at first thought of a vlookup to do it. However I do not know which column will contain the information and wanted a formula to search that would skip the blanks in the row and return the first entry it finds.

    My data table is as such:

    Column A - unique user ID to search on
    Columns B to F - one of these columns, and only one of these, will contain an entry and I am looking for that entry to be returned; data is a text string

    Thank you!

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,647
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Vlookup (or other) with blanks in columns

    Welcome to the Board!

    Try:


    A B C D E F G H I J
    1 User ID User ID Response
    2 1 one 1 one
    3 2 two 2 two
    4 3 four 3 four
    5 4 three
    6 5 six
    7 6 five
    8 7 seven
    Sheet5

    Worksheet Formulas
    Cell Formula
    I2 =LOOKUP("zzzzzz",INDEX($B$2:$F$10,MATCH(H2,$A$2:$A$10,0),0))
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup (or other) with blanks in columns

    That worked beautifully, thank you! At first I thought I needed to replace the "zzzzzz" as they were a placeholder for a cell but it works just like that!

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,647
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Vlookup (or other) with blanks in columns

    Quote Originally Posted by rduncan2019 View Post
    That worked beautifully, thank you! At first I thought I needed to replace the "zzzzzz" as they were a placeholder for a cell but it works just like that!
    Indeed! If you are looking for a text value, that parameter needs to be something that will be after anything you might find in your data. So "zzzzzz" will generally fill the bill!

    Glad to help!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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