Lookup Help

[markwilliams2004]

I am trying to lookup a heading of a column and am having difficulty with it.

The spreadsheet looks like this:

Field Number 1 2
07107 01/31/08 02/28/08
07108 06/30/08 07/31/08

Basically, I want to retrieve the values 1 or 2 based on the criteria of field number and month date.

So if I want to know what period 07/31/08 is for field 07108, I would like the formula to return the value of 2 but I have been trying combinations of index and match but I can't seem to get it to work.

Thanks in advance.

 

[AnotherHiggins]

The best way to resolve your problem is to restructure your data into a normalized table. Your example data would look like:
[tt]
Field
Number date period
07107 1/31/2008 1
07107 2/28/2008 2
07108 6/30/2008 1
07108 7/31/2008 2[/tt]

Excel has a myriad of analysis and reporting tools available, but they are geared towards "properly" stored data. With the above example data, you could use:
[tab]=SUMPRODUCT((A2:A5 = LkUpField) * (B2:B5 = LkUpDate) * (C2:C5))
where LkUpField is the field you're looking for and LkUpDate is the date you're looking for.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

What John is telling you is that Excel's features don't work nearly as well if your data structure is poorly designed. faq68-5184 explains what a good data srrcuture looks like, so that you'll be able to make use of Excel's plethora of data related features.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

Alright. I thought that may be the way I had to go.

Essentially, the table I was trying to extract the data from calculates the life of the fields over 16 and 24 months depending on the type of field.

I though there maybe a trick to quickly get at the period.

I'll have to normalize it I guess from that structure.

Thanks

 

[AnotherHiggins]

also have a look at Skip's faq68-5287

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.