Return a value based on two criteria

**statlerhale0** · 11-13-2014, 02:01 PM

Hey everyone, if anyone could help me with this that would be great.

I have a sheet that contains around 400 employees, including their managers that they report to. Every week it is updated with changes to different teams, new hires, terminations, etc. and I just paste the new data with the date of the change on the same sheet.
I also have another sheet where I keep track of customer credit requests that each employee submits and the date that they submit them.
My question is, I need to have the name of the employee's manager populate next to their name, and it has to pull from the sheet of all total employees. It also has to pull from who their manager was when they actually submitted the credit request. Is there a way to use a vlookup that is dependent on two different values (the name of the employee and the date of his request)? I've tried Index and Match, but can't get them to work.
Thanks

**Glenn Kennedy** · 11-13-2014, 02:03 PM

Without seeing the way your sheet is laid out, it is hard to tell if VLOOKUP will work. However, INDEX-MATCH will work... Can you post a suitably anonymised Excel sheet?

**statlerhale0** · 11-13-2014, 02:17 PM

Test.xlsx

So the three columns on the left show the date the request was submitted and who submitted it. The three columns on the right shows which employee belongs to which manager. Let's say the team roster changed on 11/3/14. I need to write a formula for column C that will autopopulate who the manager was of the employee dependent on the date that the credit request was submitted. Let me know if that doesn't make sense though.

Thanks

**Glenn Kennedy** · 11-13-2014, 02:20 PM

It worked...

**Glenn Kennedy** · 11-13-2014, 02:25 PM

Hi. I think that this is what you need. It is an array formula and needs to be set with CTRL + SHIFT + ENTER, not just enter (don't type the curly brackets: Excel will add them - if they're there, you will know that the formula will work OK).

**Glenn Kennedy** · 11-13-2014, 02:27 PM

Sorry, I forgot to add a term to get rid of the irritating #N/A errors that it puts there when it doesn't find a match. Use this, instead

**newdoverman** · 11-13-2014, 03:04 PM

I interpreted your requirement to mean that the dates in column A combined with the name in column B are submitted in columns E and F. If the date matches OK return the Manager Name. If the date from column A is between the dates in column F don't change Manager name unless the date in column A is equal to or later than the date in column F.

**statlerhale0** · 11-13-2014, 03:42 PM

Thanks for your help, both of you. That is perfect!
+1 Rep

**newdoverman** · 11-13-2014, 04:48 PM

Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Glenn Kennedy** · 11-13-2014, 07:28 PM

Likewise, thanks for the feedback.

Return a value based on two criteria

LinkBack

Thread Tools

Rate This Thread

Display

Return a value based on two criteria

Re: Return a value based on two criteria

Re: Return a value based on two criteria

Re: Return a value based on two criteria

Re: Return a value based on two criteria

Re: Return a value based on two criteria

Re: Return a value based on two criteria

Re: Return a value based on two criteria

Re: Return a value based on two criteria

Re: Return a value based on two criteria

Thread Information

Users Browsing this Thread

Similar Threads

Return a value based on 2 criteria being met

[SOLVED] return a value based upon one of three criteria

Return name based on 2 criteria

Return value based off 2 criteria.

return MAX based on criteria

Bookmarks

Bookmarks

Posting Permissions