VLOOKUP type technique with dual criteria

[Festus Hagen]

Hi All

Excel97

3 sheets:

• Summary
• Data
• Names

Data sheet:

• Row1 = Column headings ...
• ColA = ID (Index number (1 thru ???))
• ColB = Names (selected via Data Validation list from the sheet "Names")
• ColC = Date
• ColD ... = Data

Dynamic Named Ranges: DataRange, DataDatesRange, DataNameRange

Names sheet:

• Row1 = Column Headings
• ColA = Index number
• ColB = Name
• ColC ... = Data

Dynamic Named Ranges: NameListRange

Example of the Dynamic range setups:
One dimension

OFFSET(Data!$C$2,0,0,COUNTA(Data!$C:$C)-1,1)

Two dimensions

OFFSET(Data!$B$2:$C$2,0,0,COUNTA(Data!$B:$B)-1,COUNTA(Data!$1:$1)-1)

The goal, To select Name criteria and Date criteria with Data Validation Lists on the Summary sheet:

1. One is a list of names (Names!$B) (Named Range = NameListRange)
2. The other is the list of dates in Data!$C (Named Range = DataDatesRange)

and fill out the summary sheet.

There would only be one row of data per Name/Date combination...

I have tried vlookup/match techniques (Don't think it's the appropriate method) as well
as index/match techniques (looks more promising) with out success, any tips pointers handouts are joyfully welcome!

Here is where I'm at on the index/match:

• H1 = Name list cell on the Summary sheet
• F2 = Date list cell on the Summary sheet

INDEX(Data!$D:$D,MATCH(H1, IF(Data!$C:$C=F2,Data!$B:$B),0))

I believe this is where it fails:

Data!$C:$C=F2

Thanks

-Enjoy
fh : )_~

 

[AlphaFrog]

Did you enter your Index-Match formula as an array formula using CTRL+SHIFT+ENTER ?

 

[Festus Hagen]

Yupsir I did ... Looking at it, it has the curly bracket surround.

I have now broken it down into it's elements and they all work!

Yet, combined into one formula and it don't work no more ... gives the glorious results of: #NUM!

Got me baffled!

Thanks for your thoughts.

-Enjoy
fh <font color="#FF0000">:</font> )_~

 

[T. Valko]

Hi All

Excel97


3 sheets:

• Summary
• Data
• Names

Data sheet:

• Row1 = Column headings ...
• ColA = ID (Index number (1 thru ???))
• ColB = Names (selected via Data Validation list from the sheet "Names")
• ColC = Date
• ColD ... = Data

Dynamic Named Ranges: DataRange, DataDatesRange, DataNameRange


Names sheet:

• Row1 = Column Headings
• ColA = Index number
• ColB = Name
• ColC ... = Data

Dynamic Named Ranges: NameListRange

Example of the Dynamic range setups:
One dimension

OFFSET(Data!$C$2,0,0,COUNTA(Data!$C:$C)-1,1)

Two dimensions

OFFSET(Data!$B$2:$C$2,0,0,COUNTA(Data!$B:$B)-1,COUNTA(Data!$1:$1)-1)

The goal, To select Name criteria and Date criteria with Data Validation Lists on the Summary sheet:

1. One is a list of names (Names!$B) (Named Range = NameListRange)
2. The other is the list of dates in Data!$C (Named Range = DataDatesRange)

and fill out the summary sheet.

There would only be one row of data per Name/Date combination...

I have tried vlookup/match techniques (Don't think it's the appropriate method) as well
as index/match techniques (looks more promising) with out success, any tips pointers handouts are joyfully welcome!

Here is where I'm at on the index/match:

• H1 = Name list cell on the Summary sheet
• F2 = Date list cell on the Summary sheet

INDEX(Data!$D:$D,MATCH(H1, IF(Data!$C:$C=F2,Data!$B:$B),0))

I believe this is where it fails:

Data!$C:$C=F2

Thanks

-Enjoy
fh : )_~

If you're using Excel 97 then you can't reference the entire column in the IF function inside an array formula.

 

[Festus Hagen]

Bingo! T. Valko ... That was it. changed it to "Data!$C$2:$C$65536" and it works.

It's a 2003 Workbook that I am re-working in Excel 97, my testing/worksheet is an existing 2003 sheet, the sheet I am trying to do this on is a sheet I created in Excel 97 in the 2003 Workbook, How bizarre, I wouldn't think that could happen, that's Application functionality and not what I would think could carry along with the workbook, but it works in it's individual elements in the 2003 worksheet, just not combined, it doesn't work at all in the Excel 97 sheet I created

Before I get any deeper in this I am going to start over in a Native Excel 97 Workbook, I vision deeper nightmares if I don't and I'm tired of the save warning ...

Thanks y'all for doing what ya do.

-Enjoy
fh <font color="#FF0000">:</font> )_~

 

[T. Valko]

Bingo! T. Valko ... That was it. changed it to "Data!$C$2:$C$65536" and it works.

It's a 2003 Workbook that I am re-working in Excel 97, my testing/worksheet is an existing 2003 sheet, the sheet I am trying to do this on is a sheet I created in Excel 97 in the 2003 Workbook, How bizarre, I wouldn't think that could happen, that's Application functionality and not what I would think could carry along with the workbook, but it works in it's individual elements in the 2003 worksheet, just not combined, it doesn't work at all in the Excel 97 sheet I created

Before I get any deeper in this I am going to start over in a Native Excel 97 Workbook, I vision deeper nightmares if I don't and I'm tired of the save warning ...

Thanks y'all for doing what ya do.

-Enjoy
fh : )_~

Good deal. Thanks for the feedback!

 

[Festus Hagen]

Just to let everyone know, I did find a corrupt sheet in this workbook, the charts sheet was corrupt, removed and recreated and all is good!

Not that it matters, I did recreate a completely new workbook, at least I have been able to get most everything out of the old one.

Thank you all for doing what you do!

-Enjoy
fh <font color="#FF0000">:</font> )_~

 

[T. Valko]

Just to let everyone know, I did find a corrupt sheet in this workbook, the charts sheet was corrupt, removed and recreated and all is good!

Not that it matters, I did recreate a completely new workbook, at least I have been able to get most everything out of the old one.

Thank you all for doing what you do!

-Enjoy
fh : )_~

Thanks for letting us know!