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
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Index Match with multiple criteria and multiple results
0
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: 4889) Jan 29, '19 at 9:30 pm
Answers
0
Selected Answer
Instead of using range addresses, like $C$7:$C$12, I used named ranges as follows.
- $C$7:$C$12 = Dates
- $D$7:$D$12 = Names
- $E$7:$E$12 = Amounts
- $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.