How to do multiple lookup and return multiple values

Hi All,

I have a spreadsheet which contains two tabs "Loans" and "SO", in Loans tab i need a formula to return multiple values based on the "Emp No" and "Installment".

Trying to achieve this by using array formula but stuck somehow. Some times there might be minor difference in the Installment and SO values, such cases needs the close values. Eg: Installment = 250.28 corresponding SO value might be 250, in this case 250 should appear.

appreciate your support to achieve this.


Thanks & Regards.

Nawas

=IFERROR(INDEX(SO!$C:$C,SMALL(IF(SO!$A$2:$A$45=Loans!$A2,ROW(SO!$A$2:$A$45)),COUNTIF(A2:A2,$A2))),"") CSE

Thanks Tim for your quick reply.. but when i tried your formula it didn't work still it is taking first value from SO and not considering based on installment amount.

I have attached the sheet for your review,if you notice Emp No: 280021103497 having several installments and SO value taking only the first value. In this case should have appear based on the installment amount, eg: installment amount 55.15 then check if exact value is there in SO, if yes then it should take exact value, or any value near to that.

Hope you understand the scenario.

Again thank you very much for the efforts..

Put this regular formula on D559 and copied down

=IFERROR(INDEX(SO!$C$2046:$C$2050,AGGREGATE(15,6,(ROW(SO!$C$2046:$C$2050)-MIN(ROW(SO!$C$2046:$C$2050))+1)/(SO!$A$2046:$A$2050=$A559),COUNTIF($A$559:A559,A559))),"")

=IFERROR(INDEX(SO!$C:$C,SMALL(IF(SO!$A$2:$A$45=Loans!$A2,ROW(SO!$A$2:$A$45)),COUNTIF($A$2:A2,$A2))),"") CSE
my fall

Thanks azumi and tim for your great work.

azumi's formula returned in the same order as per the installment amounts. However, tim's formula also works but needs to do a sorting.

Very much appreciated both of your efforts...