I call my question as nested vlookup

[mtasin]

I could not figure out what title to give to my question and hence...

My question is I have a set of contract numbers with repayment schedule (showing each installment from inst. no. 1 to 48)..with the breakup principal, interest, insurance...

Now in another sheet I want to just pull All contract numbers and their respective Interest amount for each installment (from 1 to 48)... i tried vlookup, index, offset, match etc but couldn't perfect the whole thing easily...

Basically I have to first look for contract no. N in repayment schedule then look for Installment number I and then pull respective Interest Amount..

 

[Hui]

Can you post a sample file somewhere ?

 

[mtasin]

Let us assume following as source file:


Contrac Inst No Remarks

2 1 qwr

2 2 re

2 3 rr

2 4 23

2 5 rt

2 6 c

2 7 ggg

2 8 hr

2 9 jnjj

2 10 jng

3 1 ppp

3 2 ttt

3 3 yyy

3 4 uuu

3 5 iiii

3 6 kkkk

3 7 lll

3 8 555

3 9 666

3 10 777

4 1 sss

4 2 cccc

4 3 $$$

4 4 ooo

4 5 mmm

4 6 nnn

4 7 bbb

4 8 vvv

4 9 xxxxx

4 10 zzzzzz


And following as expecyed output file:


Contract 1 2 3 4 5 6 7 8 9 10

2 qwr re rr 23 rt c ggg hr jnjj jng

3

4


By the way I think yesterday night I found a solution to this..I used the following formula:

=OFFSET($A$1,(SUM(MATCH(F7,$A$2:$A$31,0),MATCH(G6,$G$6:$P$6,0))-1),2)


Assuming source & output in same sheet.. source> A2:C31, Output > Installment no 1 to 10 from G66 and 'Remarks' value for contract no. 2 and installment no. 1 in F7.


If you find something needs to be perfected here please let me know..


Thank you so much.. !

And yes the solution does not contain any vlookup ;-)

 

[mtasin]

^^^^^^^

I think there is li'l mess up in sample n source data posting...

source> contract no 2,3,4..Inst No 1 to 10 and Remarks like qwr, re, rr...


Output file

Contract no 2,3,4... Inst No 1 to 10 in rows..And remarks value for each contract for respective inst no..

 

[Hui]

mtasin

You could add a helper column Column C and put in C2: =A2&"-"&B2

then on Sheet 2 B2: =INDEX(Sheet1!$D$1:$D$100,MATCH($A2&"-"&B$1,Sheet1!$C$1:$C$100,0))

and copy across and down

your example is here:

http://rapidshare.com/files/396204792/mtasin.xls.html

 

[mtasin]

Thanks Hui ! That was quite clever thinking... i think i'll remember creating this kind of helper cells in future..