Reference range cells from VLOOKUP results

**bobprivate** · 10-22-2009, 11:55 AM

I have a workbook where one tab contains the data that I am given:

Project-A data1 data2 date1 date2 date3
Project-B data1 data2 date1 date2 date3
Project-C data1 data2 date1 date2 date3
Project-D data1 data2 date1 date2 date3

In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.

So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.

thanks in advance for taking a look and helping me solve this..

**NBVC** · 10-22-2009, 12:01 PM

You can use Max(IF()) array formula

eg.

=MAX(IF(Sheet1!$A$1:$A$100=X1,Sheet1!$B$1:$E$100))

adjust ranges and references to suit and confirm by holding the CTRL and SHIFT keys down and then press ENTER

**bobprivate** · 10-22-2009, 12:11 PM

ok.. if my sample data was:

Sheet1
A B C D E F
1 Project-A data1 data2 date1 date2 date3
2 Project-B data1 data2 date1 date2 date3
3 Project-C data1 data2 date1 date2 date3
4 Project-D data1 data2 date1 date2 date3

Sheet2
A B C D E F
1 Project-C ???
2

What would be the formula in C1 that would tell me the maximum of
Sheet1!D3 thru Sheet1!F3; given the data in Sheet2!A1.

Bearing in mind, of course, that I won't really know ahead of time
that the data is on row-3.

**NBVC** · 10-22-2009, 12:20 PM

=MAX(IF(Sheet1!$A$1:$A$100=A11,Sheet1!$D$1:$F$100))

assuming a maximum of 100 rows in Sheet1....

then confirm with CTRL+SHIFT+ENTER so that formula becomes surrounded by { } brackets

**bobprivate** · 10-22-2009, 01:07 PM

So far that hasnt worked for me - Would you please have a look at my attached workbook at:

https://sites.google.com/site/exceldemofile/

**NBVC** · 10-22-2009, 01:09 PM

Link doesn't work... post it here as an attachment (paperclip icon in Reply box)

**bobprivate** · 10-22-2009, 01:14 PM

ok.. see attachment.. thanks!

**NBVC** · 10-22-2009, 01:59 PM

See attached,

Formula used:

=IF(A4="","",MAX(IF(Database!$B$2:$B$7=Report!A4,Database!$I$2:$M$7)))

confirmed with CTRL+SHIFT+ENTER and copied down.

Cells are formatted as dates.

I also add IF() part to leave cell blank if Project not entered in A.