Lookup two criteria and return a third criteria as result. Aaaargh!

Hi,

Tough to explain so I have attached a sample sheet for you to play. Your help and advice is appreciated... DOING MY HEAD IN!

I have a table with data in it. I also have a timetable (separate to data table). I need the timetable to LOOKUP the data table for TWO criteria and, if it finds a match, return a THIRD result in the cell on the timetable that corresponds to the first two criteria. Simple, right!

So, we have Client, State and Date in the data table. I need the timetable to insert the client name in the appropriate state column on the corresponding date row.

Probably easier to see example. If you need clarification, let me know!

Test_tracker_example.xlsx

Hi

I've inserted a new column A. alongside the data table I've concatenated the date and the state. This forms the basis of a vlookup table.

I've replaced the results in your output table with a formula. =IF(ISERROR(VLOOKUP($M23&S$6,$A$27:$E$35,2,FALSE)),"",VLOOKUP($M23&S$6,$A$27:$E$35,2,FALSE))Test_tracker_example.xlsx

Does this help?

Chris

TheCman81...

Thank you for welcoming me, and THANK YOU FOR HELPING MEEEE!!!

YOU ARE AMAZING!! Has anyone ever told you that?? AMAZING with a capital A-M-A-Z-I-N-G !!!

Any good at conditional formatting? If I had existing conditional formatting on those Client cells in the data table, is there any way of retaining that formatting when getting the result in the timetable?

Eg,
Client one is red in the data table, based on conditions from other data in the data table.
Client two is blue in the data table, based on conditions from other data in the data table.
Client three is green in the data table, based on conditions from other data in the data table.

Is there a way to tell your AMAZING formula to retain the formatting?

Chris 53...

Thank you! You're pretty AMAZING too!! What world have I stumbled into?? I love this place!!

Thank you both soooo much for your help. You have made one person extremely happy!! It's the small things in life, you know?!

Both work and both have taught me something.

AAAA - MMMM - AAAA - ZZZZ - IIII - NNNN - GGGG !!!!

Welcome to the world of Excel geeks. I used to be big headed enough to think I knew a lot about excel. Then someone pointed out just a little thing that I had not come across and I realised that in reality I know so little about excel that it's humbling.

Example of what I mean by the formatting, and what I'd like the timetable to look like...

Test_tracker_example_V2.xlsx

Nope. It's a certain colour for a particular step in the process.

Say we were delivering socks.

Step 1
Ordered (red)

Step 2
Order Processed (blue)

Step 3
Order picked (purple)

Step 4
Order packed (orange)

Step 5
Order sent (green)

Step 6
Order delivered (yellow)

The conditional formatting (colour change) is based on particular columns in the data table being populated (not blank). The examples I uploaded didn't have all of the columns. I created a basic table for people to play with, hoping for ideas, suggestions and examples of ways I can do it (if possible). I'm thinking it's going to have to be a macro, or I can duplicate the conditional formatting from the data table sheet to the timetable sheet, adjusting the source. In the real workbook, the data table is on one sheet and the timetable is a separate sheet.

I've had a little play, but I'm still finalising the conditional formatting on the data sheet so, I haven't been able to make solid results. Any advice you can give is appreciated!

Almost!

I have attached another example. This one has the two sheets I mentioned previously and more closely reflects the columns (or steps) in the table I want to colour code.

I'd like them to remain colour coded in the data table, as well as reflect the colour (step) the 'client' is up to in the timetable (without the data table losing it's colours). I'm hoping my example will make sense to you. You seem likely to help me achieve my goal!

Let me know if you have more questions, need more clarity, or if I'm just being stupid and it's not possible.

Did I mention that I appreciate your help???? Thanks so much Cman!!

Test_tracker_example_V3.xlsx