Hello guys,
I am stuck with this index array formula, hope anyone can help me out.
Please see attached file where I need to extract (ProductID into J2:J6) the smallest 5 records based on amount column that match criteria values in Column H & also in Column D the amount need to be greater than 0.
Last edited by ibuhary; 03-09-2016 at 10:21 AM.
Sorry guys forgot the attachment earlier. Now its there.
Enter array formula in J2 and copy across to Col L and then down
Formula:
Please Login or Register to view this content.
Enter array formula in M2 and copy down
Formula:Please Login or Register to view this content.
***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
v H I J K L M 1 MATCH ProductID Description Class Amount 2 B 0100102 Name B 4500 3 0100105 Name B 3000 4 0100110 Name B 500 5 0100113 Name B 2500 6 0100118 Name B 7 8 9
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I am afraid, you have not matched multiple items in Column H.
In my file I have mentioned that you need to match items in H2:H3 (i.e B & C).
Can you show what the final outcome should look like?Originally Posted by ibuhary
ProductID Description Class Amount
0100110 Name B 500
0100116 Name C 1000
0100108 Name C 1500
0100113 Name B 2500
0100105 Name B 3000
Try this one
Formula:Please Login or Register to view this content.
***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
Copy across all the way to column M and then down
v H I J K L M 1 MATCH ProductID Description Class Amount 2 B 0100105 Name B 3000 3 C 0100108 Name C 1500 4 0100110 Name B 500 5 0100113 Name B 2500 6 0100116 Name C 1000 7 8 9
Last edited by AlKey; 03-10-2016 at 10:56 AM.
few points for your note as follows:
1. I don't see the smallest record (in terms of Amount on Top), i think instead you have sorted out based on product ID. I need the order based on "Amount" column; and
2. What if I have more than two records in Column H to match with, e.g. 10 items??? There should be some way to refer to them as H2:H11 rather than building the formula by referring to each one of them individually.
Please note that the example file is only an extract from my database. Actually my database runs for over 200 thousand lines with categories over 20, out of which I am trying to extract smallest 100 records that match around 10 categories for those amounts are not equal zero.
Here is another way with a helper column ( col E )
1. Enter formula below in E2 and copy down
Formula:Please Login or Register to view this content.
now you can enter as many class values in Col H as you want and the range will adjust automatically.
2. for Amounts enter formula in M2 and copy down
Formula:Please Login or Register to view this content.
3. now enter this formula in J2 and copy across to L2 and down
Formula:Please Login or Register to view this content.
Please see attached workbook.
I have converted the data range to a table and used the filters.
This gives you the bottom 6 - see attached.
Will this work for you?
peterrc
I am sorry, but I am looking for extracting the data via formula. I am unable to apply sorting or filter or helper columns to the database.
Last edited by ibuhary; 03-13-2016 at 12:52 PM.
Thanks a lot guys, I managed to do it with the following array function:
{=INDEX($A$2:$A$21,MATCH(SMALL(($D$2:$D$21)*--(IFERROR(MATCH($C$2:$C$21,$H$2:$H$3,0),0)>0)*($D$2:$D$21>0),COUNTA($D$2:$D$21)-SUMPRODUCT(--((IFERROR(MATCH($C$2:$C$21,$H$2:$H$3,0),0))>0)*--($D$2:$D$21>0))+ROW(J2)-ROW($J$2)+1),$D$2:$D$21,0),1)}
It works find provided there are no duplicate values in amount column.
" I need the order based on "Amount""
And this is precisely why your approach will not work. Duplicate values.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks