Index Match with multiple criteria and multiple results

0

 hi, i used it and its good but when i used search formula in my actual sheet it not working im sending pls check it. 

it only shows when it took loan, and if take salary it should not show it

regds waseem

Answer
Discuss

Discussion

You have modified your question in such a way as to make it impossible for third parties to follow our conversation. Please ask a new question. But before you do, please make sure that you declare your named ranges on workbook level (in the Name Manager, select Scope as Workbook). Also make sure that you spell the names of your named ranges correctly in the formulas. That will be easier if you make them shorter. When you define your question always show the cell where you want the answer and an example of the correct answer to be found.
Variatus (rep: 2523) Jan 29, '19 at 9:30 pm
Add to Discussion

Answers

0
Selected Answer

Instead of using range addresses, like $C$7:$C$12, I used named ranges as follows.

  1.  $C$7:$C$12 = Dates
  2.  $D$7:$D$12 = Names
  3.  $E$7:$E$12 = Amounts
  4.  $F$7:$F$12 = Desc

It's easier to manage dynamic ranges when they are named. But if you prefer you can replace the names in the formulas below with addresses, like  $C$7:$C$30.

The formula velow will retrieve the date. It uses Excel's AGGREGATE function instead of an array formula.

=IFERROR(AGGREGATE(15,6,Dates/((Names=$E$3)*(Desc=$F$3)),ROW()-6),"")

The next formula is more like the one you had. It's an array formula and retrieves the amount. It stumped for a while because I tried to get the row number of the found date.  I didn't succeed. The formula relies on dates being in consecutive order.

=IFERROR(INDEX(Amounts,SMALL(IF((Names=$E$3)*(Desc=$F$3),ROW(Dates)-ROW(INDEX(Dates,1,1))+1),ROW()-6)),"")

Both are designed for use in the 7th row of your worksheet. This is to provide a counter for the SMALL() function. In row 7 ROW()-6 = 1, in row 8 ROW()-6 = 2 etc. So, you need to change the fixed number 6 to a number one smaller than the row in which you have the original formula before copying down.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login