Request assistance to figure out a formula to pull data onto a table based on multiple criteria. I tried using the INDEX and MATCH formula but it gave an error. In screenshot below and in attached file as well, I have mentioned the requirement. Please advise and help.
Will be helpful if could get a solution without VBA as I don't have much knowledge about it.
Scrolable TableJPG.JPG
Attachment 623155
Last edited by cherias; 05-09-2019 at 06:46 AM. Reason: Added the updated screenshot
Hello @DavesExcel,
Thanks for the suggestion but doesn't look like its working or at least I am not able to make it work.
I tried using this formula : =SUM((A3:A21=I2)*(F3:F21="In Progress")*(B3:B21)) [ Column A = Priority No#, Cell I2 = Combo Reference, Column F = Status, Column B = Description]. So basically, I am putting criteria's on Priority and Status Column, so I can get the corresponding value in Description and that too only unique descriptions.
The approach I am trying to follow is to have the separate table (show below) and get the extract from raw onto this, using formula. Then on the scrollable table I will use the index formula or offset formula to get the data.
Scrolable Data Table Extract.JPG
Last edited by cherias; 05-06-2019 at 01:53 PM.
Still the same using [=SUMPRODUCT((Data!A3:A21=Data!I2)*(Data!F3:F21="In Progress")*(Data!B3:B21))] . I am missing something and cant figure out what ? I tried by getting it into array formula as well (ctrl+shift+enter) but no working. Please advise.
Last edited by cherias; 05-06-2019 at 11:01 PM.
In H5 then copied across
=IFERROR(INDEX(INDEX($A$3:$F$25,,MATCH(H$4,$A$2:$F$2,0)),AGGREGATE(15,6,ROW($A$3:$A$25)/(($A$3:$A$25=$I$2)*($F$3:$F$25="In Progress")),ROWS($H$5:$H5))-ROW($A$3)+1),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thank you for the help. However, I have few queries :-
1) Will the formula change, if the extract has to be pulled to a different sheet than raw data. I tried applying the same formula on another sheet [Scrol Table Data], its not giving me results
2) I assume that it wont matter if we have additional columns added in between the rows of the Raw Data
3) Is it mandatory that the Raw Data has to start with a specific row, which is used in formula?
In A2 then copied across
=IFERROR(INDEX(INDEX(Data!$A$3:$F$25,,MATCH(A$1,Data!$A$2:$F$2,0)),AGGREGATE(15,6,ROW(Data!$A$3:$A$25)/((Data!$A$3:$A$25=$I$2)*(INDEX(Data!$A$3:$F$25,,MATCH("Status",Data!$A$2:$F$2,0))="In Progress")),ROWS($A$2:$A2))-ROW($A$3)+1),"")
Last edited by kvsrinivasamurthy; 05-07-2019 at 10:46 AM.
Re: Lookup cell value based on multiple criteria
Hello kvsrinivasamurthy,
This works perfect. Thanks a lot for the help. Much appreciate the support. *
Just a last query, how do we remove duplicates from the extracted list.
Like as shown in the screenshot, the same risk no or description is showing multiple times due to the related entries in raw data. But how do get only unique entries based on unique description.
Remove Duplicate from Extracted Data.JPG
Last edited by cherias; 05-08-2019 at 09:51 PM.
Duplicate means all the columns should have same data as shown in screen shot. Even one column data differs then it is not duplicate.
Considering Column A and Column B together for duplicate here is the formula. Additional columns do not effect.
In A2 then copied across.
=IFERROR(INDEX(INDEX(Data!$A$3:$F$25,,MATCH(A$1,Data!$A$2:$F$2,0)),AGGREGATE(15,6,ROW(Data!$A$3:$A$25)/((COUNTIFS($A$1:$A1,INDEX(Data!$A$3:$F$25,,MATCH("Risk No",Data!$A$2:$F$2,0)),$B$1:$B1,INDEX(Data!$A$3:$F$25,,MATCH("Description",Data!$A$2:$F$2,0)))=0)*(Data!$A$3:$A$25=$I$2)*(INDEX(Data!$A$3:$F$25,,MATCH("Status",Data!$A$2:$F$2,0))="In Progress")),1)-ROW($A$3)+1),"")
Last edited by kvsrinivasamurthy; 05-09-2019 at 02:07 AM.
Works absolutely perfect .. Thanks a ton ..
Welcome. Thanks for feed back.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks