+ Reply to Thread
Results 1 to 12 of 12

Help Querying Entire Column

  1. #1
    Registered User
    Join Date
    06-13-2019
    Location
    New York, NY
    MS-Off Ver
    Mac
    Posts
    6

    Question Help Querying Entire Column

    Hi everyone, new user and Excel novice here and my first post. I am looking for a formula that will help me with the following.... I have a cell (A1) I would like to compare against an entire column (C:C). If cell A1 matches *any* of the cells in Column C I would like it to place the value of cell B1 into column D right next to the matching cell (otherwise the cell should remain blank). Note that there is is only *one* value in column A (A1), and *one* value in column B (B1). I can figure this out with dragging down a formula from D1 to all of the cells in the column but that is not what I want. I want the formula in cell D1 to dynamically check the entire length of column C without having to drag down the formula (similar to how vlookup works). Any help is appreciated!

    -adk749

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2019
    Posts
    5,263

    Re: Help Querying Entire Column

    So you want a formula in D1 to change a value in a different row?

    That's not how it works...
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    06-13-2019
    Location
    New York, NY
    MS-Off Ver
    Mac
    Posts
    6

    Re: Help Querying Entire Column

    Looking to put a formula into column D that will output the value in B1, if and only if the value in column C matches the value in A1. Does that make better sense?

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2019
    Posts
    5,263

    Re: Help Querying Entire Column

    In D1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and copy down.

  5. #5
    Registered User
    Join Date
    06-13-2019
    Location
    New York, NY
    MS-Off Ver
    Mac
    Posts
    6

    Re: Help Querying Entire Column

    That formula works but I would like something I do not need to "copy down". I would like something that dynamically checks the entire column for me, that's where I am having trouble.

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2019
    Posts
    5,263

    Re: Help Querying Entire Column

    As I said in post#2, you can't enter a formula in D1 which changes the value in for example, D10...

  7. #7
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2019
    Posts
    5,263

    Re: Help Querying Entire Column

    If you mean you want D1 to show the value in B1 if ANY cell in column C matches the value in A1, then you could use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-13-2019
    Location
    New York, NY
    MS-Off Ver
    Mac
    Posts
    6

    Re: Help Querying Entire Column

    Looking for the formula to continually check down the column and compare each subsequent cell with A1.
    For example, compare D1 with A1, then compare D2 with A1, then D3 with A1, and so on.
    If the cell in column D matches with A1, I want it to output B1 for every match.

  9. #9
    Registered User
    Join Date
    06-13-2019
    Location
    New York, NY
    MS-Off Ver
    Mac
    Posts
    6

    Lightbulb Re: Help Querying Entire Column

    I have to imagine there is a way to accomplish this - forum experts don't fail me now :-).

  10. #10
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2019
    Posts
    5,263

    Re: Help Querying Entire Column

    If you want to change a value in a cell using a formula, you need to have a formula in that cell.

    If you want to change a value in a cell without having a formula in it, you need to use VBA or a query.

    You can imagine all you like - that's how Excel works.

    As you requested in your first post, I have provided a formula solution - if you want a VBA or query solution, I'm sure I can provide that too...

    (and now I'm going back to my self imposed rule of avoiding threads started by Mac users...)

  11. #11
    Registered User
    Join Date
    06-13-2019
    Location
    New York, NY
    MS-Off Ver
    Mac
    Posts
    6

    Re: Help Querying Entire Column

    Any type of solution would be greatly appreciated!

  12. #12
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2019
    Posts
    5,263

    Re: Help Querying Entire Column

    Please Login or Register  to view this content.

+ 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