Find a value in a row and column, return intersecting cell value

  • I have a spreadsheet where I need to find a call value that is the intersection of a date and a name


    On Sheet 1 - Row 2 contain the cells with dates in them; customer formatted as d, do only show the day.
    Column A contains names.


    In a cell, on another sheet (Sheet2), I have a cell (A1) with the value of =today() to return today's date. It also contains the list of same names (starting in A2 to A182)


    What I am looking to do is create a formula on Sheet 2 in column B, that takes the name in the same row in column A, and the date in cell A1, and finds the value in the cell that is the intersection of those on Sheet 1.


    For example using the below table, the formula would look for Name 4 and January 14 and return "Birch"


    [TABLE="width: 500"]

    [tr]


    [td]


    [TABLE="width: 864"]

    [tr]


    [TD="class: xl92, width: 64, bgcolor: transparent"]

    [/tr][/td][tr]


    [TD="class: xl78, width: 64, bgcolor: transparent"]A
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]B
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]C
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]D
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]E
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]F
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]G
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]H
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]I
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]J
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]K
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]L
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]M
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]N
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]O
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]P
    [/TD]
    [TD="class: xl78, width: 64, bgcolor: transparent"]Q
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]1
    [/TD]
    [TD="class: xl95, bgcolor: white"][/TD]
    [TD="class: xl93, bgcolor: #92CDDC, colspan: 16"]January
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]2
    [/TD]
    [TD="class: xl96, bgcolor: white"][/TD]
    [TD="class: xl91, bgcolor: yellow"]1
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]2
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]3
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]6
    [/TD]
    [TD="class: xl91, bgcolor: yellow"]7
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]8
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]9
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]10
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]13
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]14
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]15
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]16
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]17
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]20
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]21
    [/TD]
    [TD="class: xl90, bgcolor: yellow"]22
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]3
    [/TD]
    [TD="class: xl80, width: 64, bgcolor: transparent"]Name 1
    [/TD]
    [TD="class: xl83, width: 64, bgcolor: navy"][/TD]
    [TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
    [TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
    [TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
    [TD="class: xl82, width: 64, bgcolor: fuchsia"]sunset 11-1
    [/TD]
    [TD="class: xl82, width: 64, bgcolor: fuchsia"]sunset 11-2
    [/TD]
    [TD="class: xl86, bgcolor: fuchsia"] sunrise
    [/TD]
    [TD="class: xl84, bgcolor: transparent"][/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]trio
    [/TD]
    [TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
    [TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
    [TD="class: xl76, width: 64, bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]4
    [/TD]
    [TD="class: xl80, width: 64, bgcolor: transparent"]Name 2
    [/TD]
    [TD="class: xl83, width: 64, bgcolor: navy"][/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]etwo
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]kick
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]kick
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]kick
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]kick
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]kick
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]kick
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]tent kick
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin nb
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]5
    [/TD]
    [TD="class: xl80, width: 64, bgcolor: transparent"]Name 3
    [/TD]
    [TD="class: xl83, width: 64, bgcolor: navy"][/TD]
    [TD="class: xl85, bgcolor: yellow"]Nina
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"] trans 10-2 pm/
    [/TD]
    [TD="class: xl85, bgcolor: yellow"]trans
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl85, bgcolor: yellow"]Nina / trans
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]hood
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]hood
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]hood
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]hood
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]hood
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]hood
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]hood
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]hood
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]6
    [/TD]
    [TD="class: xl80, width: 64, bgcolor: transparent"]Name 4
    [/TD]
    [TD="class: xl83, width: 64, bgcolor: navy"][/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
    [/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
    [/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]lieu
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]Birch
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]Birch
    [/TD]
    [TD="class: xl79, width: 64, bgcolor: lime"]Birch
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]lieu
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]lieu
    [/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
    [/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
    [/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]7
    [/TD]
    [TD="class: xl80, width: 64, bgcolor: transparent"]Name 5
    [/TD]
    [TD="class: xl83, width: 64, bgcolor: navy"][/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
    [/TD]
    [TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]tent blink
    [/TD]
    [TD="class: xl89, width: 64, bgcolor: #339966"]moby
    [/TD]
    [TD="class: xl89, width: 64, bgcolor: #339966"]moby
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]8
    [/TD]
    [TD="class: xl80, width: 64, bgcolor: transparent"]Name 6
    [/TD]
    [TD="class: xl83, width: 64, bgcolor: navy"][/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
    [/TD]
    [TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]tent blink
    [/TD]
    [TD="class: xl89, width: 64, bgcolor: #339966"]moby
    [/TD]
    [TD="class: xl89, width: 64, bgcolor: #339966"]moby
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl92, bgcolor: transparent, align: right"]9
    [/TD]
    [TD="class: xl80, width: 64, bgcolor: transparent"]Name 7
    [/TD]
    [TD="class: xl83, width: 64, bgcolor: navy"][/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]vacation
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
    [/TD]
    [TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
    [/TD]
    [TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl77, width: 64, bgcolor: yellow"]Media
    [/TD]
    [TD="class: xl88, width: 64, bgcolor: #B1A0C7"]tent blink
    [/TD]
    [TD="class: xl89, width: 64, bgcolor: #339966"]moby
    [/TD]
    [TD="class: xl89, width: 64, bgcolor: #339966"]moby
    [/TD]

    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [/TABLE]



    I've thought of using some combo of index/match and maybe vlookup, but hitting a wall.

  • Re: Based on selected call, return list all values in A:A and column of selected cell


    I'm not sure how you would get a formula to work based on a selected cell.
    I'm not a formula expert, but I would say that's probably not possible.
    You are probably going to need code to do this.

  • Re: Based on selected call, return list all values in A:A and column of selected cell


    Quote from skywriter;775614

    I'm not sure how you would get a formula to work based on a selected cell.
    I'm not a formula expert, but I would say that's probably not possible.
    You are probably going to need code to do this.




    You are not a formula expert, but respond to a post as if you know the answer, and that it isn't possible? What is the point?


    This is possible. I have had a formula do this in the past. Actually multiple formulas that I had to copy down, which I am OK with. If I hadn't lost the dang spreadsheet that I had this working in before I would be in great shape but . . .

  • Re: Based on selected call, return list all values in A:A and column of selected cell


    Quote from t33p33;775622

    You are not a formula expert, but respond to a post as if you know the answer, and that it isn't possible? What is the point?


    This is possible. I have had a formula do this in the past. Actually multiple formulas that I had to copy down, which I am OK with. If I hadn't lost the dang spreadsheet that I had this working in before I would be in great shape but . . .


    Nice of you to edit your original post and then criticize me.


    Your original post said using a formula that was based on a cell you picked.


    I still say that won't work, neither will removing that part of it and getting all uppity with someone who was trying to help you with what you originally posted.


    You do know when you edit a post it's time stamped that you edited it, right?


    Have a nice day.

  • Re: Based on selected call, return list all values in A:A and column of selected cell


    Yes genius. I am well aware of time stamping. The reason that was changed is because as I continued to work on this I was halfway through solving it and updated the post so that anyone that actually knew what they were doing would be providing an answer to my question at the time, rather than the entire solution. The solution, which by the way, I figured out today and have working. Go pat yourself on the back and troll someone else, or learn Excel and actually offer some help.

  • Re: Find a value in a row and column, return intersecting cell value


    Any by the way, it works off a selected cell, because I actually put a static cell into the formula. The static cell contains the formula that actually selects which cell across the top I am looking for, allowing me to enter a single cell into the formula and bypass what you say is impossible entirely. You are correct. Not possible if you look at it straight on, but what I was asking for was help with both the creativity and formula. You were not helping in either. If you intend to help, don't just drop a note about something being impossible. Instead learn Excel, learn creating thinking about how to solve the problems and offer real solutions. It is amazing how those types of things are seen then as help.

  • Re: Find a value in a row and column, return intersecting cell value


    I'm amazed you would post such disparaging, acerbic comments in a public thread, never mind a private thread or message.


    In the context of your question as originally written, a valid point was made - however, and I'm guessing here, it was not expanded on as you specifically asked for a formula based solution. However, it could have been the basis for further discussion and refinement and possibly, ultimately, a formula based solution as you requested.


    On this board Bruce's post count is >50x yours; of those 640 odd posts I can't find any that were threads started by him - while some posts may have been requests for further information the majority are answers - I won't even count the 2300+ on another forum (and I'm not going to bother checking his post count on yet more forums).


    Quote

    Instead learn Excel...


    Personally I would accept any answer, whether a formula or VBA, posted by Bruce without question, but that's just me. It may be that it could be improved on, but that's the nature of forums - on a free forum nobody spends hours analysing a problem to come up with the optimum solution. If you want that level of commitment then pay for it.


    There's an important word in that last sentence - you neither pay to post an issue or are expected to pay if a solution is provided. Essentially you rely on the goodwill of others giving their time freely to help you solve your problems. Posting comments like those above will only dissuade others from replying in case you react the same way.


    Quote

    learn creating thinking about how to solve the problems


    Yes - that would be good if you did.


    I've had my say - No need for you to reply as I will not view this thread again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!