• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

index and match with multiple criteria to pull unique data

rrocker1405

rrocker1405

Member
Hi,

Attached is a sample data and I'm trying to pull data that match the criterias in highlighted cells to populate information for me in columns j through o. I can generate the values using a single criteria for levels and id however I dont know how to include multiple criteria to pick up information. any suggestions?

I'm presently using a formula with a named range (single criteria) and to pick up unique values...
=IFERROR(INDEX(ID,SMALL(IF(($G$17=Level)*(COUNTIF($I$16:I16,ID)=0),ROW(Level)-MIN(ROW(Level))+1,""),1)),"")

Thanks in advance.

Kind regards,
A!
 

Attachments

  • Index Match.xlsx
    10.7 KB · Views: 10
Hi Nebu,

Thank you for the quick update on the request. However, the ID column should pick up unique numbers and at this point in time it shows up same numbers but I think I can resolve it for now.

Thanks once again.

Kind regards,
A!
 
Hi,

In the sheet that I included it has 7 filter's and the formulas work if there are values against each of it. the idea is to help users to navigate to the specific list which would also mean that they need not choose all the 7 filters and should be able to show up all the unique values tracing to ID.

This is similar to what you would get as an output in pivot. Attached sheet is for reference with what I'm trying to achieve. Any suggestions, how to pick up these information?

Thanks in advance.

Kind regards,
A!
 

Attachments

  • Copy of Index Match.xlsx
    16.1 KB · Views: 8
Hi Anand ,

See your file ; I have put in the formula only in the ID column.

Narayan
 

Attachments

  • Copy of Index Match.xlsx
    16.7 KB · Views: 9
Put in I17:

=IFERROR(INDEX(A$4:A$13,SMALL(IF(($G$17=$B$4:$B$13)*($G$18=$C$4:$C$13)*($G$19=$D$4:$D$13)*($G$20=$E$4:$E$13),ROW(Level)-MIN(ROW(Level))+1,""),ROWS(A1:$A$1))),"")

Aray FOrmula, and copied down and cross
 
Hi Narayan,

I tried with your formula, renamed the named range and could not find the desire outcome whereas in the sheet sent by you it just works fine. I dont understand what is that I'm doing incorrectly. I also found in the sheet j13 has a formula which does not have any relevance. could you please help me understand what should be done here? i'm clueless...

Hi azumi,

I tried your formula as well but does not work. I get only empty cells.

Note: i've used ctrl+shift+enter as required.

Thanks in advance.

regards,
A!
 
Hi Narayan,

I have attached the sheet that I'm working on presently with the same formula and logic stated by you in the sheet earlier but I cannot generate the value(s). Please let me know what is that I'm doing incorrectly?

Thanks in advance.

Regards,
Anand
 

Attachments

  • test.xlsx
    64.5 KB · Views: 4
Hi Anand ,

You did everything right , but the references were off by 1 ; the first IF statement was :

IF(Entity=IF($C$3<>"",$C$3,INDIRECT($B$3)),

when it should have been :

IF(Entity=IF($C$4<>"",$C$4,INDIRECT($B$4)),

See the file.

Narayan
 

Attachments

  • test.xlsx
    67.2 KB · Views: 5
Hi Narayan,

Thank you for the quick update on the query and explaining what went wrong. However, I would like to know why does the formula does not take in to account ID # 1 - 8? which matches the criteria of a business request?

Thanks in advance.

Kind regards,
A!
 
I also realize that it takes in account of blank values of Entity type and shows up in the calculation (e.g. ID # 14 & 15)
 
Hi Anand ,

You need to change one more number in the formula ; the corrected one is :

=IFERROR(INDEX(ID,SMALL(IF(Entity=IF($C$4<>"",$C$4,INDIRECT($B$4)),IF(Department=IF($C$5<>"",$C$5,INDIRECT($B$5)),IF(Domain=IF($C$6<>"",$C$6,INDIRECT($B$6)),IF(Sprint=IF($C$7<>"",$C$7,INDIRECT($B$7)),IF(Release=IF($C$8<>"",$C$8,INDIRECT($B$8)),IF(Vendor=IF($C$9<>"",$C$9,INDIRECT($B$9)),IF(Text=IF($C$10<>"",$C$10,INDIRECT($B$10)),ROW(ID)-13))))))),ROWS($A$1:$A1))),"")

where the number is highlighted.

This number should always be one less than the minimum value in ROW(ID) ; since this is 14 ( since your named range ID starts from row 14 ) , this number should be 13.

In your first file , ID started from row 4 , because of which the number was 3.

Narayan
 
Hi Narayan,

Thank you for your quick update. I would like to know what if the data is another sheet? the named range should automatically pick up the formulas, isnt it? Please correct me if i'm wrong here

I've updated the formula but does not show up values, dont know what am i doing wrong.

Thanks in advance. Kind regards,
A!
 
Hi,

Attached is the sheet that im working presently.

1. I was able to calculate when all the filtered information is available but as soon as one of the filtering value is not available it does not calculate. (sheet 2)

2. the sumproduct value does not actually calculate the count of values completely where as the pivot includes all the data as required and presents the detailed over but for analysis and presenting as reporting pivoting does not help.(Metrics sheet)

Thanks in advance.

Kind regards,
A!
 

Attachments

  • Test.xlsm
    777.8 KB · Views: 2
Hi Anand ,

I am putting in the formula for the last time.

See your file now.

Narayan
 

Attachments

  • Test.xlsm
    776.5 KB · Views: 16
Hi Narayan,

I do not have words to thank you for this effort. I was seriously stuck in this step to proceed further and I'm grateful as always, a million thanks.

I was not aware that for the indirect I'd have to use "Named range" and I was using the cell header directly which was the issue.

Wish you a nice evening. You've been a life savior!!!

Kind regards,
A!
 
You must log in or register to reply here.
Back
Top