Lookup One Value from multiple identical rows

**KimC33** · 11-29-2011, 03:34 PM

Hello, I am new to the forum and was hoping you could help me out with a formula that has been plaging me the last few weeks.

I have attached a spreadsheet with an example of what I am looking for in more detail.

To summarize, I have a large workbook that I manually maintain that contains information about resources. A worksheet in the this workbook has all the summary info for every resource including role, location, projects working on, billable hours, resource type, etc. This info is pulled from several external sources through lookup formulas and links. Most of the formulas work great and I have no issues. I do have issues when there are more than one data point that the lookup formula is pulling from. In the example attached, you will see more detail and a better description of how I am trying to automate the process. But basically, I need a formula to lookup a resource name in the raw data and give either the project name or task name based on the most recent time sheet that was submitted.

I just do not know how to do this and this has been plaging me for weeks now. I have been manually going into the raw data to find this information when needed, but it would be great if it could be listed with all the other summary information I have generated, I have tried to combine IF statements with lookups but it is not working.

Please let me know if this is even possible. I am currently using Excel 2007. Thank you so much in advance for your help!

Kim

**jeffreybrown** · 11-29-2011, 03:48 PM

Maybe this...

B6 =MAX(IF($A$16:$A$28=A6,$D$16:$D$28)) >> copy down

C6 =INDEX(B$16:B$28,MATCH($B6&$A6,$D$16:$D$28&$A$16:$A$28,0)) Copy over and down

These formulas should be confirmed with Ctrl + Shift + Enter

**NBVC** · 11-29-2011, 03:53 PM

In B6, try:

=MAX((INDEX(($A$16:$A$28=A6)*($D$16:$D$28),0)))

copied down

In E6:

=INDEX(B$16:B$28,MATCH(1,INDEX(($A$16:$A$28=$A6)*($D$16:$D$28=$B6),0),0))

copied down and to next column

**KimC33** · 11-29-2011, 04:54 PM

Thank you Jeffrey, I tried your formulas but unfortunately they did not work in my example, unless I was doing something wrong.

NVBC - I tried your formulas on my example and they worked perfectly! I changed the dates and projects to make sure it would update and it did, so thank you for that. But when I tried to transfer it into my actual working spreadsheet I am getting an #VALUE error message. I changed the info to match my real data, but it is not calculating correctly. On my real workbook, I run this monthly report every month with updated data that I just replace the old data. The number of line items varies every month and usually increases, so most of my previous formulas use the entire column instead of the cells so that it captures everything. I tried doing this with your formula but it didnt work. I also tried changing it to naming the cells but incorporating many more then neccessary so when I run the report next month it will capture everything. Not sure which way is the best way to calculate the value and respond to the changing number of rows.

The formulas for the project and task names work when I manual enter the date for the time sheet date, but not when I try to use the formula for the date. Please refer to the updated attachement. This is basically the real worksheet I am working from, minus the personal info. All the columns are in the correct placement on this file then on my my file. You can see in row 4, I manually entered the date and in Row 3, I added the formula, but its not calculating. Can you help again?

;Thank you SOOOOO Much!!

Kim

**NBVC** · 11-29-2011, 05:01 PM

Since you are "multiplying" two ranges together, you can't have text in any of the cells... therefore start the ranges at row 2 instead to not include the column headers.

=MAX((INDEX(('Time Tracking'!$A$2:$A$10000=$B3)*('Time Tracking'!$I$2:$I$10000),0)))

You may also have to format the cells as dates.

Note also that these are array formulas and can slow you down if you use too many of them or if the ranges are too large... try reducing the ranges to the maximum needed. The same goes for the INDEX/MATCH formulas I gave you.. try not to use whole column references....

**KimC33** · 11-29-2011, 05:25 PM

Thank you so much, that worked. You rock!! That is going to save me so much time.... I may have to come back and ask you a few more questions on other worksheets I am working on. :-)

Thank you again!!!