Result in Excel from Lookup Table with 2 Criterion 1

[beadedbytes]

What options (formula or otherwise) do I have for getting the 'Result' listed below from the following 'LOOKUP TBL'?

LOOKUP TBL
Dt Max Dt Min Dt Range
9/15/2008 9/15/2007 00-12 mos
9/14/2007 9/15/2006 13-24 mos
9/14/2006 9/15/2005 25-36 mos
9/14/2005 9/15/2004 37-48 mos

Actual Dt Result
4/12/2008 00-12 mos
4/12/2007 13-24 mos
4/12/2006 25-36 mos
4/12/2005 37-48 mos

 

[SkipVought]

Hi,

Using named ranges...
[tt]
Result: =INDEX(Dt_Range,MATCH(A10,Dt_Max,-1),1)
[/tt]


Skip,

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

 

[beadedbytes]

Thanks, Skip, for such quick feedback.

I am familiar with the index/match functions, but feel that I need some clarification.

I am looking for the 'Result' to be 00-12 mos,etc. The formula is currently returning a value in Dt Max column.

Please let me know. Thank you.

 

[GlennUK]

The formula is currently returning a value in Dt Max column.

In that case I'd say that you've created the formula incorrectly. Skip's formula indexes Dt_Range, and not Dt Max.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[beadedbytes]

In case I was not clear in the initial request, the lookup table is comprised of 3 columns of information -- 'Dt Max', 'Dt Min', 'Dt Range'.

The dates in 'Actual Dt' column need to be assigned information from the 'Dt Range' column. This information should be placed in the 'Result' column.

 

[SkipVought]

It is PERFECTLY clear!

It just happens that one of the columns is REDUNDANT data.

Did your TRY the solution I posted? It actually works as expected!

Skip,

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

 

[SkipVought]

Having recreated the test, copying your example data and my posted solution, (took less than 1 minute), here are my results, copy 'n' pasted from my sheet...
[tt]
Actual Dt Result
4/12/2008 00-12 mos =INDEX(Dt_Range,MATCH(E2,Dt_Max,-1),1)
4/12/2007 13-24 mos
4/12/2006 25-36 mos
4/12/2005 37-48 mos
[/tt]
Doesn't appear to be any different than your desired result.

BTW, I placed my result table in a different location on the sheet than earlier.

Skip,

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

 

[beadedbytes]

Sorry, Skip. I feel like a real dunce, but have not been able to get your solution to work. Have created it 2 times. The first effort returned the same date range for all 'Actual Dt' -- 37-48 mos. The 2nd time returned NA error message.

Much appreciated the assistance and fast response.

 

[SkipVought]

Do you have CALCULATE on?

Did you create Named Ranges for each column of data?

Skip,

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

 

[beadedbytes]

Calc F9 was on and disabled. Still did not work.

And, yes, I created named ranges.

 

[SkipVought]

Is the LOOKUP reference (first argument in MATCH) pointing to the correct value under Actual Dt?

Skip,

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

 

[SkipVought]

Are the values in Dt Max and Actual Dt, REAL dates?

You can easily tell by selecting those ranges, Format > Cells > Number TAB -- Category GENERAL and report what happens.

Skip,

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

 

[beadedbytes]

Skip,
Thanks for sending me on the right path. I got it to work by doing 2 things--

1) referencing Dt-Min instead of Dt_Max
2) changing match_type to 1 instead of -1

Much appreciated!

 

[SkipVought]

BTW, EITHER way works. I know. I originally tested it with BOTH ranges, with 1 & -1 respectively in MATCH.

Therefore, you have some DATA anomoly in Dt_Max.

Skip,

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

 

[beadedbytes]

Please forgive me for not extending this star earlier. Cheers.