VLOOKUP Nesting problem (or Data Match problem..not too sure)

[ofagirl]

I am trying to solve the following problem:
I have the following two tables, the first table has the data I will be using, and the second table is the table I wish to sort my data into
(Sheet 1) Table 1

Date	CurveID	DaystoMaturity	ZeroRate
3/24/2006	14	1	0.0102
3/24/2006	31	7	0.0412
3/27/2006	14	31	0.0101
3/27/2006	31	62	0.0231
3/28/2006	14	9	0.0402
3/29/2006	14	6	0.0103
3/29/2006	31	28	0.0314

<tbody>
</tbody>

(Sheet 2) Table 2 for Curve ID 14

1	A	B	C	D	E
2		DaystoMaturity
3	Date	0	1	7	30
4	3/24/2006
5	3/27/2006
6	3/28/2006
7	3/29/2006

<tbody>
</tbody>

What I am trying to do is fill table 2 with the zero rate that corresponds with the correct date and the DaystoMaturity closest to the column value. For example, in cell D6 on Sheet 2, I wish to place the zero rate 0.0402 as that was the zero rate on 3/28/2006 with 9 days to maturity, which is closest to 7 out of the possible days to maturity in table 2. Furthermore, I need to check whether or not the CurveID is 14, if it is, include the value, otherwise do not.

I need help figuring out a formula that can do this, I tried some nested vlookup stuff, but am unsure how to incorporate the fact that the days to maturity do not necessarily correspond exactly.

Thanks for any help!

 

[Andrew Poulsom]

Welcome to MrExcel.

With your second table in the range A1:E6, try in D3 confirmed with Ctrl+Shift+Enter and copied down and across:

=INDEX(Sheet1!$D$2:$D$8,MATCH(MIN(IF(Sheet1!$A$2:$A$8=$A3,ABS(Sheet1!$C$2:$C$8=B$2))),IF(Sheet1!$A$2:$A$8=$A3,ABS(Sheet1!$C$2:$C$8=B$2)),FALSE))

 

[ofagirl]

Thanks for your help - this seems to be working, except when I copy it over to column B, there is a reference error...but I'll take a closer look. Much appreciated again!

 

[ofagirl]

Actually, I can't seem to figure out why there is no reference for column B (days to maturity 0) - I think it is because I am unsure why in the part of the function ABS(Sheet1!$C$2:$C$8=B$2, why B$2 is being used instead of D$2. Perhaps you can help explain this to me?

 

[Andrew Poulsom]

Sorry, I meant in B3 confirmed with Ctrl+Shift+Enter and copied down and across.

 

[ofagirl]

Thanks that makes more sense now! However I am having another problem (sorry!)
In reality, my spreadsheet contains much more data, and I have several dozen various DaystoMaturity for each Date. When I apply this formula, for some reason it does return the ZeroRate corresponding to the correct date, however the ZeroRate it returns is the first one in the list, NOT the one that matches most closely to the DaystoMaturity. In other words, the formula does not seem to be "searching though" all the various DaystoMaturity for a particular Date to find the one that matches most closely.

 

[Andrew Poulsom]

Can you post some sample data where the formula I posted doesn't return the required result please?

 

[ofagirl]

Certainly, here is some data that would be for table 1 sheet 1 (blanks are 0 - for some reason they are not showing up) and ignore the discountfactor

MarkAsOfDate	CurveID	DaysToMaturity	ZeroRate	DiscountFactor
24-Mar-06	14	2012	0.04361959	0.788312247
24-Mar-06	14	1096	0.04234464	0.881771254
24-Mar-06	14	915	0.04210397	0.900817434
24-Mar-06	14	731	0.04186271	0.920374415
24-Mar-06	14	549	0.04170376	0.939805831
24-Mar-06	14	367	0.041547	0.959496445
24-Mar-06	14	185	0.04044443	0.979909835
24-Mar-06	14	94	0.03941987	0.98999715
24-Mar-06	14	61	0.03900045	0.993565642
24-Mar-06	14	31	0.03847029	0.996768918
24-Mar-06	14	7	0.0380946	0.999276551
24-Mar-06	14	3	0.03776303	0.99969256
24-Mar-06	14		0.03776303	1
24-Mar-06	14	1280	0.04259262	0.862605651
24-Mar-06	14	1826	0.04333737	0.806952848
24-Mar-06	14	2741	0.04452805	0.718406783
24-Mar-06	14	2194	0.04389921	0.770263945
24-Mar-06	14	2376	0.04409552	0.752822985
24-Mar-06	14	2558	0.04429399	0.735625273
24-Mar-06	14	2922	0.04476225	0.701599844
24-Mar-06	14	3287	0.04511256	0.669150598
24-Mar-06	14	3471	0.04572589	0.650549332
24-Mar-06	14	3653	0.0463392	0.63224282
24-Mar-06	14	3839	0.04642276	0.61712408
24-Mar-06	14	4018	0.04650651	0.602846782
24-Mar-06	14	4203	0.04659449	0.588378892
24-Mar-06	14	4385	0.04668357	0.574418608
24-Mar-06	14	1461	0.04283962	0.843947352
24-Mar-06	14	1645	0.04308904	0.825196741
24-Mar-06	14	3106	0.04493753	0.685115961
27-Mar-06	14	92	0.03950833	0.990187281
27-Mar-06	14	1281	0.04288837	0.861629975
27-Mar-06	14	1096	0.04263855	0.881009582
27-Mar-06	14	917	0.04246292	0.899816791
27-Mar-06	14	731	0.04228653	0.919609606
27-Mar-06	14	549	0.04182865	0.939632913
27-Mar-06	14	1463	0.04313748	0.842765777
27-Mar-06	14	184	0.04032998	0.980072765
27-Mar-06	14	7	0.0380946	0.999276551
27-Mar-06	14	63	0.03911448	0.993336205
27-Mar-06	14	31	0.03850412	0.996766108
27-Mar-06	14		0.03788573	1
27-Mar-06	14	1	0.03788573	0.99989718
27-Mar-06	14	4202	0.04613457	0.591506151
27-Mar-06	14	365	0.04137209	0.959877136
27-Mar-06	14	3654	0.04575227	0.635804758
27-Mar-06	14	1645	0.04337715	0.82414858
27-Mar-06	14	4018	0.04600453	0.606111565
27-Mar-06	14	3837	0.04587802	0.620744059
27-Mar-06	14	4383	0.04626469	0.577395219
27-Mar-06	14	3472	0.04555438	0.651507163
27-Mar-06	14	3287	0.04535598	0.667717731
27-Mar-06	14	3108	0.0451792	0.683572059
27-Mar-06	14	2922	0.04499891	0.700300993
27-Mar-06	14	2010	0.0438717	0.787428145
27-Mar-06	14	2557	0.04458418	0.734251868
27-Mar-06	14	2376	0.04435531	0.751578462
27-Mar-06	14	2192	0.04412562	0.769422901
27-Mar-06	14	2741	0.04479184	0.717016113
27-Mar-06	14	1827	0.04361893	0.805745896
28-Mar-06	14	2557	0.04495965	0.73236523
28-Mar-06	14	2376	0.04473632	0.749757182
28-Mar-06	14	2192	0.04451228	0.767676975
28-Mar-06	14	2010	0.04427886	0.785702551
28-Mar-06	14	1826	0.04404574	0.804159171
28-Mar-06	14	1645	0.0437832	0.822673881
28-Mar-06	14	731	0.04266843	0.91892113
28-Mar-06	14	1280	0.04329153	0.860538345
28-Mar-06	14	1098	0.0430646	0.879701418
28-Mar-06	14	916	0.04286574	0.899030077
28-Mar-06	14	2743	0.04517846	0.714808016
28-Mar-06	14	365	0.04147819	0.959777362
28-Mar-06	14	549	0.04207291	0.939294854
28-Mar-06	14	1462	0.04352067	0.841599042
28-Mar-06	14	4202	0.04657879	0.58855707
28-Mar-06	14	7	0.03819643	0.999274636
28-Mar-06	14	31	0.03852108	0.996764699
28-Mar-06	14	184	0.04041337	0.980032383
28-Mar-06	14	4383	0.04670473	0.574421207
28-Mar-06	14	92	0.0396093	0.990162568
28-Mar-06	14	4018	0.04645304	0.603193617
28-Mar-06	14		0.03793668	1
28-Mar-06	14	2922	0.04539265	0.698145696
28-Mar-06	14	62	0.03913339	0.993438503
28-Mar-06	14	3837	0.0463308	0.61786274
28-Mar-06	14	3653	0.04620892	0.633049083
28-Mar-06	14	3471	0.04598409	0.648989686
28-Mar-06	14	3289	0.04576166	0.665171954
28-Mar-06	14	3107	0.04557729	0.681394212
28-Mar-06	14	1	0.03793668	0.999897043
29-Mar-06	14	61	0.03927062	0.993521644
29-Mar-06	14	30	0.03857392	0.996864653
29-Mar-06	14	7	0.03821344	0.999274316
29-Mar-06	14	1	0.03790275	0.999897134
29-Mar-06	14	92	0.03969342	0.990141984
29-Mar-06	14	365	0.04189451	0.959386024
29-Mar-06	14	548	0.042463	0.93886337
29-Mar-06	14	915	0.04327383	0.898234563
29-Mar-06	14	1280	0.04373318	0.859234895
29-Mar-06	14		0.03790275	1
29-Mar-06	14	733	0.04304224	0.918033628
29-Mar-06	14	184	0.04067992	0.979903318
29-Mar-06	14	1097	0.04350847	0.878656034
29-Mar-06	14	2924	0.0456795	0.696407565
29-Mar-06	14	4202	0.04684542	0.58679434
29-Mar-06	14	4018	0.04672119	0.601456114
29-Mar-06	14	3837	0.04660038	0.616153876
29-Mar-06	14	3653	0.0464799	0.631373336
29-Mar-06	14	3471	0.046277	0.647225135
29-Mar-06	14	4383	0.04696982	0.572637288
29-Mar-06	14	3106	0.04587638	0.679784788
29-Mar-06	14	2375	0.04508237	0.748198545
29-Mar-06	14	2742	0.04548346	0.713295624
29-Mar-06	14	2557	0.04528698	0.73072473
29-Mar-06	14	2192	0.04487903	0.766024863
29-Mar-06	14	1826	0.04442283	0.802676345
29-Mar-06	14	1645	0.044191	0.821195762
29-Mar-06	14	1461	0.04395854	0.84025591
29-Mar-06	14	3288	0.04607541	0.663418942
29-Mar-06	14	2010	0.04465078	0.784129921

<tbody>
</tbody>

and I am imputting it into the following table

	DaysToMaturity
MarkAsOfDate		1	7	30	60	90	180	360	540	720	900	1080	1260	1440	1620	1800	1980	2160	2340	2520	2700	2880	3060	3240	3420	3600	3780	3960	4140	4320
3/24/2006
3/27/2006
3/28/2006
3/29/2006
3/30/2006
3/31/2006
4/3/2006

<tbody>
</tbody>

 

[Andrew Poulsom]

Oh, there's a silly error in my formula (= instead of -). Try:

=INDEX(Sheet1!$D$2:$D$121,MATCH(MIN(IF(Sheet1!$A$2:$A$121=$A3,ABS(Sheet1!$C$2:$C$121-B$2))),IF(Sheet1!$A$2:$A$121=$A3,ABS(Sheet1!$C$2:$C$121-B$2)),FALSE))

Sorry about that.

 

[ofagirl]

Wonderful! Thanks so much for clarifying