Lookup from Bottom cell to Top cell

[kheldar]

Hello,

I am once again in need your expertise.

I've got a specific table format.

I need to lookup the value(will be located at the top) of a cell that is located at the bottom. Another thing to note that, I'll be looking up multiple values from bottom to top and at the top two of the ranges will be merged.

I have been trying to solve this on my own but I have failed. All the solutions I've encountered work with from top to bottom.

I'm attaching a sample workbook to show you what I need.

Edit to make it clearer as per suggested:

A note: all the employee names will be unique I was just lazy to change all the names. Sorry for that. So we can assume that Employee 4 and others have unique names.

In the sample file, employees' teams, lunch hours and shift is listed. It's how we get the data. It'll always be in this format. Since, almost all of the people who will use the file are not really good at Excel, I want to keep it as simple as possible for them. They just have to copy paste it from the source and the source is exactly in that format.

So, in another sheet, I'll enter employee's name then I need to look up their lunch hour, shift and team. Shift portion is listed at the top. For example "QUEUE ASSIGNMENT FOR 18:00" which is H1 merged cells. So I need to lookup A1 or H1 depending on the employees assignment.

The file will serve a purpose to track employees' working habits. We do content moderation and I'll be performing bunch of other things with the data I want to lookup which I can do by myself. Again all of which I've learnt from experts like you in this community.

The spreadsheet will include thousands of raw data which I'll be organizing using other techniques.

The data comes from an e-mail. It's how it's formatted in the e-mail. And copied as is in the file. Again it'll always be in the same format as I uploaded in the sample.

I really hope I'm clear enough.

Once again, I'd really appreciate your help.

Thanks in advance.

[#2]

@kheldar 

You've had 67 views, but not a single reply. And this one isn't really an answer to your question. Rather, it's a suggestion that you start over and make far more clear what it is that you're trying to do.

I need to lookup the value(will be located at the top) of a cell that is located at the bottom.

Huh?! That sentence alone makes no sense. Could you give the specific here...what is the value, and where is it? Which value that is "at the top of a cell at the bottom"--??

I will offer one observation that is extremely important: you have spent a LOT of time making this spreadsheet colorful; some might call it "pretty," which might be appropriate at some point. But of greater importance is creating something that, you  know, actually works, actually functions. Leave the fancy coloring and formatting till a later step; once it works.

You can help us help you by backing up and describing what the purpose is that's being served here.

• What is the big picture into which this fits?
• What is the spreadsheet supposed to include?
• Where does data come from in the first place?
• What's supposed to happen with it?
• That kind of information.
• Based on your question, you appear to be wanting to do something more than just have a pretty sheet to hang on a bulletin board somewhere. But what is that bigger something?

[#3]

Thank you for your response. I really appreciate you taking your time to give a detailed answer. I didn't realize that I wasn't clear enough. So I've just edited my post with clearer explanation. I didn't actually format anything. It just how the source data is prepared. I just copied it as is. I only created sheet 2 to demonstrate what I need.

[#4]

What is to be done with Employee 4 who appears in ALL groups, BOTH shifts and ALMOST EVERY Lunch time.

[#5]

I@Doug_Robbins_Word_MVP 

I was just lazy to change all the names :) They will all of course have different names. So we can assume every employee is unique.

[#6]

I don't get it. You're lazy and you expect someone else to use their time to help you.
If you want help, provide a real example of the data taken directly from the email message. You also need to confirm that the layout will be IDENTICAL each time a new email with the data is received.

[#7]

@kheldar 

You wrote: 

The data comes from an e-mail. It's how it's formatted in the e-mail. And copied as is in the file. Again it'll always be in the same format as I uploaded in the sample.

And before we go any further, I need to ask--which is what I'd do if we were sitting down face-to-face--whether there's any possibility to change that source data? Frankly, that's what I'd want to do. That current array of colorful data is just not laid out in a manner that makes it readily searchable for what you are wanting to retrieve, described in this sentence:

So, in another sheet, I'll enter employee's name then I need to look up their lunch hour, shift and team.

I'll grant you that it's arrayed in such a manner that it is somewhat appealing for a human to look at , going in reverse--knowing their Team (and name) already, using that to find their own lunch break.

But going in, via Excel, starting with the name (as you are seeking to do) and finding the team and lunch hour.... that's a different task, a task for which this is not at all a helpful array.

Hence my question: is there any possibility to change that source data?

Do you know how it's generated in the first place? Is there a database somewhere, and an extraction routine running on that database to produce this layout? Could you (we) get back to that original source?

[#8]

I didn't think It'd matter that's why I was lazy. But I should have made it clearer. You are right. It's important not to skip these kinds of details. I was just too much focused on my point of view rather than what others would think. Since I'm know every detail about my job, I couldn't think that someone would need that kind of detailed. I was wrong.

Yes the layout will be identical every time a new e-mail is sent.

[#9 · Accepted solution]

Unfortunately, it's not possible to change the data source format as my colleagues would refuse that. It's prepared in Excel and pasted into Outlook. However, as I have gathered from all your comments that It's really difficult to perform such a task, I have come up with my own solution. It's not ideal and requires lots of labor which is against Excel's nature and I wanted to avoid that and that's why I asked you experts to step in. My observation has been that in programming, there is always a solution to everything but It's not always that easy.

My solution:

Since the data source will always be in the same format,  I'm creating direct cell references in another sheet and organizing the table in a vlookup or index match friendly way. It's a lot of work and not really ideal but it's a solution.

[#10]

@kheldar 

You wrote:

Unfortunately, it's not possible to change the data source format as my colleagues would refuse that.

To which I'd suggest that maybe "colleagues" is the wrong term, since it usually implies a degree of collegiality or cooperation in the advancement toward shared outcomes. Doesn't require that, however, so you can continue describing them that way. Or maybe you could build on it and see if they would, in fact, be able to be flexible and collegial.....   Of course, it all depends on how important your task is in the grand scheme of things.

You also wrote of your solution:

Since the data source will always be in the same format,  I'm creating direct cell references in another sheet and organizing the table in a vlookup or index match friendly way. It's a lot of work and not really ideal but it's a solution.

Actually, if your colleagues won't flex their format, your solution is quite reasonable, so long as the data is always in the same format, with no additional rows or columns ever being added.

[#11]

Your solution sounds familiar to the one that I would have been proposing and if it was done in the right way, when a new set of data is received, if you paste it into the workbook in which you have setup your solution, you will not need to repeat that manual work.