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

1. 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

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

Hi and welcome

Please see updated tracker, I've overwriten the Unavailable purely for this senario

Test_tracker_example(1).xlsx

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

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

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

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?

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

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 !!!!

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

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.

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

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

Test_tracker_example_V2.xlsx

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

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

Where does the colour coding in your data table come from? Is it a certain colour for a specific client?

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

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!

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

Please see the attached and let me know if you can work with that?

I've used conditional formatting with a similar formula used in your table, this works off there being certain text within the example conditons (see column E)

Test_tracker_example_V2.xlsx

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

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