• 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.

need help using index match countifs function on filtered cells only

Asfandyar Ali

Asfandyar Ali

Member
Hello respected members. sorry folks, i couldn't post in default forum as im new so posting here.
i need help in extracting data using index match and countifs function. if i use below formula it gives me result from hidden cells also. i need result applied only on visible cells.

=INDEX(Sheet2!A:A,MATCH(1,COUNTIFS(Sheet1!A8,Sheet2!C:C,Sheet1!$B$1,Sheet2!B:B),0))
Please see example worksheet as an example. Need urgent help please. Thanking you in advance.
 

Attachments

Hi Ali ,

Instead of asking for a formula , please specify what you want to do ; on Sheet1 what should appear in column A , and what should appear in column B. This output is in no way related to whether you have a fillter in place on the data in Sheet2.

Narayan
 
Hi Narayank. on sheet 1 and on cell B8 result should be #NA because it is being filtered in Sheet2. if you notice formula goes into the filtered cells and extract the value and write it in B8.

SITE ID BTS INSTALLATION Result should be
TXA2625 #N/A #N/A
TXA1417 #N/A #N/A
TXA1418 #N/A #N/A
TXA1500 #N/A #N/A
HWY1526 10210039181-15 10210039181-15
MRE7714 10210040171-14 #N/A
BHB7169 10210040171-14 10210040171-14
HWY2126 10210040171-14 10210040171-14
TXA1374 #N/A #N/A
TXA1375 #N/A #N/A
HAL2121 #N/A #N/A
ATK1062 #N/A #N/A
ATK1102 #N/A #N/A
ATK1801 #N/A #N/A
 
Hi Ali ,

I think I have not made myself clear ; can you specify what you want the output to be , and how it is to be obtained ? If you want the output should change depending on what the filter applied to Sheet2 is , then this cannot be done using formulae ; it can be done using VBA. Is this acceptable ?
 
Hi Narayank.
Sorry im not familar with VBA codes. i'll try to elaborate further, i need the same formula to lookup values on filtered/visible cells. is there anyway to use subtotal function instead of countifs because subtotal only see filtered/visible cells unlike countifs. sorry if im creating mess here :)
 
Hi Ali ,

The problem with using SUBTOTAL is that it can only output numeric values ; it cannot be used like the INDEX function to retrieve actual data which is present in cells. Even COUNTIF can only count , it cannot retrieve data from cells.

From what I can understand , you want the data which is in column A on Sheet2 ( po_number ) to be retrieved and put in column B on Sheet1 , if the following conditions are satisfied :

1. The site ID in column A on Sheet1 should match the site ID in column C on Sheet2

2. The data in column B on Sheet2 ( PO_CATEGORY ) should match the header in cell B1 on Sheet1

3. If you have filtered the data on Sheet2 to display only Type = "A" in column D on Sheet2 , then the data should transfer the po_number only for Type = "A" ; if you have filtered the data on Sheet2 to display only Type = "B" in column D on Sheet2 , then the data should transfer the po_number only for Type = "B"

The problem with using formulae is that we can figure out that there is a filter which has been put , but we cannot recognize what the filter criterion / criteria are. VBA can make this easy.

Narayan
 
Dear Narayank.
Yes great. this is what i need. could you please help me with the vba code? i would be grateful and it would be big help
 
You must log in or register to reply here.
Back
Top